28 Triggers in MySQL

Bhumika Shah

Introduction

•  Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.

• Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).

Why Triggers

• Triggers help you to enforce business rules

• Triggers help you to validate the data even before they are inserted or updated

• Triggers help you to keep log of records like maintaining audit trail

• Triggers help you to enforce security authorization

Limitations

•  Triggers increase overhead on system as they are called on each update/insert , wherever applied which results into system running slower

• It is difficult to view the triggers as compared to viewing constraints , relationships, indexes etc.

Syntax of Trigger:

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

[trigger_order]

trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Events of Trigger

• Trigger time is the action time of the trigger. Action refers to before or after which denotes the trigger is to be activated before or after each row to be processed.
• Trigger event specifies the action which will activate the trigger.
• The trigger event allowed are :
• Insert : Whenever a new row is inserted
• Update : Whenever existing row is modified
• Delete : Whenever existing row is deleted

Trigger Body

• Body contains various statements to be executed when trigger activates. But if the trigger body contains multiple statements you must use Begin… End block.

• Certain statements not permitted in stored procedures are

• Lock…unlock table

• Alter table

• Alter View, etc…:OLD and :New

• To refer columns in trigger body two aliases are used , :old and :new

• :old – It refers to existing row before it is updated or deleted.

• :new – It refers to the new row which is to be inserted or and existing row after it is updated.

Defining a Trigger

   To create a new trigger , Right click on Table and select alter table.

Once you click Alter table , table screen open which has multiple tabs, Select Trigger tab and you can see the different types of trigger available

You will require one LOG table to see the effect of Trigger execution.

Structure of LOG table is as follows: (empid, name , salary , action, upd_date)

Example of After Insert Trigger

Use ‘test’;

Create trigger emp_AIns after insert on emp for each row

Begin

Insert into emp_log values(new.empid, new.name, new.salary, ‘Insert’ , now());

End;

After writing the code for the above trigger you can test it by inserting a row in emp table and you will notice the corresponding effect in emp_log table.

Let us look at another example for Before Insert Trigger

Before Inserts are usually used for validations and after insert are used for Logs

Before Insert

Create trigger emp_BIns before insert on emp for each row

Begin

If new.salary=0 then

Set new.salary = 100;

End if

End;

Here, we keep minimum salary as 100, So , if salary as 0 is entered , it is changed to 100.

You can test the trigger by inserting a new row, and entering 0 as salary.

Similarly examples for Before Update and After Update are done , And the same rule applies , before is validating and after is maintaining log

Here, we have created one more table name emp_log1(Username,description) wherein we maintain the user information about which user updates the data.

After Update

Create trigger emp_AUPD after update on emp for each row

Begin

Insert into emp_log1 values(user(), concat(‘Update employee Salary , Name : ‘, old.name, ‘OLD

salary :’, old.salary , ‘New Salary : ‘ , new.salary ) );

End;

Test after Update

First check the data in emp_log1

Select * from emp_log1;

You will not get any rows

Then, view the data in emp table

Then issue the following statement :

Update emp set salary = salary + 500; (Note : we are not giving where condition , hence will affect all rows)

Now , issue following statement

Select * from emp_log1;

Similarly, in before update , we check the salary updated should not be less than the existing salary.

Similar type of trigger like Before Insert.

References:

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers

2) Learning PHP, MySQL & JavaScript , Robin Nixon , O’Reilly Publications

3) PHP and MySQL Web Development , Luke Welling , Laura Thomson , Pearson Publications, Fourth Edition

4) MySQL Documentation : https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

5) www.mysqltutorial.org

Additional Reading:

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers

2) MysQL documentation : dev.mysql.com/doc/