The following walkthrough describes how to create triggers. Trigger is a routine executed under certain conditions.
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:
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:
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.
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:
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)
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.