MySQL Triggers

Triggers in SQL

Originally published in dataqoil.com.

Triggers in SQL is a way to invoke something as a response to the events on the table in which Trigger is attached. The example of the event can be Insert, Update, Delete. Triggers are of two type, Row Level and Statement Level. The row level trigger is triggered for each row while statement level trigger is triggered once per transaction or execution.

Why do we need trigger?

• In Data Engineering or Data Pipelining, to reflect the change of the data without having to listen.
• To perform data validation with the by executing trigger Before inserting data. Examples can be performing integrity checks.
• To handle database layer errors.
• To record the history of the data changes.
• To achieve some kind of table monitoring functionalities.

Triggers in MySQL

MySQL provides only row level triggers.

Syntax

CREATE TRIGGER name_of_trigger
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
body_of_trigger;

Trigger’s body can be a single line to multiple and it is enclosed inside BEGIN and END for multiple line body.

• While using Update, we can access existing value and new value (existing as Old and new as New)and we can compare between them too. Example: to compare old and new value of a column age, we can do OLD.age != NEW.age.
• While using Insert, we can access new value using New keyword.
• While using Delete, we can access old value using Old keyword.

Alert After Insert

Lets insert into logs after inserting the values.

1. First of all, lets create a database, Student via MySQL.

create database Student;

1. Create table, student_bio.

create table Student.student_bio (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        `name` varchar(255),
                        class varchar(255),
                        age float
                        );

1. Create table, student_logs

CREATE TABLE Student.student_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name varchar(255) NOT NULL,
    student_age float NOT NULL,
    created_date DATETIME DEFAULT NULL,
    operation VARCHAR(50) DEFAULT NULL
);

1. Create a trigger to log info on logs on inserting.

CREATE TRIGGER Student.after_student_insert 
    after insert ON Student.student_bio
    FOR EACH ROW 
 INSERT INTO Student.student_logs
 SET operation = 'insert',
     student_name = new.name,
     student_age = new.age,
     created_date = NOW();

1. Insert few data into it.

INSERT into Student.student_bio values(1,'John', 5, 15), (1,'Johnny', 7, 25);

1. Now look into Student.student_logs

Alert Before Insert

Lets insert the logs before inserting the values.

1. Define a trigger as:

delimiter // 
CREATE TRIGGER Student.before_student_insert 
    before insert ON Student.student_bio
    FOR EACH ROW 

 begin
 INSERT INTO Student.student_logs (student_name, student_age, created_date, operation) values(new.name, new.age,now(), 'insert_before');
 end
 //
 delimiter ;

1. Now insert few data as:

INSERT into Student.student_bio(`name`, class, age) values('Diwo', 5, 15), ('Ben', 7, 25);

1. Now see the data of student_logs

Alert Before Update

Lets create a trigger which checks the new value before inserting. If new value is greater than old, then set age as average of them. Else set age as old age. And additionally, insert the logs too.

1. Create a trigger as:

 delimiter // 
CREATE TRIGGER Student.before_student_update
    before update ON Student.student_bio
    FOR EACH ROW 

 begin
if old.age<new.age then set new.age=(old.age+new.age)/2;
    else set new.age=old.age; 
 end if;
 INSERT INTO Student.student_logs (student_name, student_age, created_date, operation) values(old.name, new.age,now(), 'update_before');
 end
 //
 delimiter ;

1. Now update student_bio as:

update student.student_bio set age =10 where class=5; 

1. Again, update student_bio as:

update student.student_bio set age =20 where class=5; 

In first update, the condition was False so the age was not changed. But in the second update, the condition is True and thus the age was set to average of two.

Alert Before Delete

Will be updated sooon….

Drawbacks

Now we knew its benefits and the use cases, lets get into the drawbacks of Triggers:

1. It increases the server overhead and can cause server hang ups.
2. It is difficult to test triggers because they are run by Database itself.
3. Can be used for advanced data validation but simple ones can be achieved by constraints like Unique, Null, Check, foreign key etc.