Creating a Trigger

The following walkthrough describes how to create triggers. Trigger is a routine executed under certain conditions.

Prerequisites

In order to complete this walkthrough, you will need access to MySQL server 5.0.13 or higher.

You should also have the following privileges:

• TRIGGER privilege on the demobase database
• ALTER, UPDATE and SELECT privileges on the dept table
• SELECT, INSERT, UPDATE and DELETE privileges on the emp table to test trigger work

Creating trigger

Let’s add a column in table dept where the sum of salary of all employees in this department will be stored. To create this column:

1. In Database Explorer, make sure that connection to your server containing the base (Demobase) is active.
2. Click the Create New SQL button on the Standard toolbar to open a new visual SQL document.

3. Type or paste the following script there:

    ALTER TABLE demobase.dept ADD COLUMN salsum FLOAT;
    UPDATE demobase.dept SET salsum = 
    (SELECT SUM(demobase.emp.sal) FROM demobase.emp
    WHERE demobase.emp.deptno = demobase.dept.deptno);
   

   First the query adds a column to a table and then fills it with data.

4. Execute the script by clicking Execute on the SQL toolbar.

Now, our column is ready and filled with correct data. But when a new employee will be added or removed from the emp table, this column should be updated too. This can be done automatically with triggers. So, let’s create triggers that will keep data in this column always up-to-date. To create these triggers:

1. In Database Explorer, make sure that the connection to the server containing the base (Demobase) is active.
2. Navigate to the node of the table demobase.emp and expand it.
3. Right-click the Triggers node and choose New Trigger on the shortcut menu. A trigger editor document will appear.
4. Type SalsumIns in the Name field.

5. Type the following text in the Trigger body field:

    UPDATE demobase.dept SET demobase.dept.salsum =
    (SELECT SUM(demobase.emp.sal) FROM demobase.emp
    WHERE demobase.emp.deptno = demobase.dept.deptno)
   

6. Switch to the Event tab.
7. Select After in Timing and Insert in Event.
8. Save the trigger.

The trigger for insert event is ready. Now, create SalsumUpd and SalsumDel triggers with the same body with after update and after delete event. After that each data change in the emp table will be reflected in the dept table.

Want to Find out More?

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.

Ready to start using dbForge Studio for MySQL?

Download a free 30-day trial

• Previous
• Next