MySQL Triggers – Part 2

We learned about MySQL triggers in our previous post. With the help of a Before Update Trigger, we learned about creating and implementing triggers. Most importantly, we also discussed the advantages and disadvantages of using triggers. I will be talking more about MySQL Triggers in this post. Also, I will be discussing the other five triggers namely, After Update, Before Insert, After Insert, Before Delete, and After Delete.

Table Strucutre

We will be using the same tables for this post as we used in our previous post. I am once again sharing create and insert statements for these tables. Initially, no values will be set in students_history table because there is no history.

CREATE table students (
id int(11) NOT NULL UNIQUE , 
name varchar(255) NOT NULL, 
rank int(11) NOT NULL);

INSERT INTO students (id, name , rank) <br> VALUES (1,'Joe', 1) ,(2,'Monika',3), (3, 'Phoebe' , 4), (4, 'Chandler',2)  ,(5 , 'Rachel', '5') , (6, 'Ross', 6);

CREATE table students_history(
id int (11),
old_rank int (11),
new_rank int (11)
);

MySQL Triggers Explained

Let me explain other 5 MySQL Triggers namely After Update, Before Insert, After Insert, Before Delete, and After Delete.

After Update Trigger

The AFTER UPDATE trigger is fired after an UPDATE operation is executed on the table with which the trigger is associated. In our example, the After Update trigger will get activated after an update query is fired on the students table. The trigger will insert a new row in students_history table. For instance, please find a sample CREATE statement for an After Update Trigger.

DELIMITER $$
CREATE trigger after_students_update
after update
on students
FOR EACH ROW
BEGIN            		-- trigger body starts
INSERT INTO students_history
SET
id = old.id,
old_rank = old.rank,
new_rank = new.rank;	        -- trigger body ends
END$$ 
DELIMITER );

An update statement on students table activates the After Update Trigger which inserts a new row in students_history table.

UPDATE students SET rank = 1 WHERE id = 2 LIMIT 1;
mysql> SELECT * FROM students_history;
+------+----------+----------+
| id   | old_rank | new_rank |
+------+----------+----------+
|    2 |        2 |        1 |
+------+----------+----------+
1 row in set (0.01 sec)

The only difference between before event and after event triggers is that the former ones are fired before the commit of insert/update/delete statements. On the other hand, the latter ones are fired after the commit of insert/update/delete statements.

Before Insert Trigger

BEFORE INSERT trigger will be fired when before any INSERT operation is executed on the table for which the trigger is associated. As an example, please find a sample Create statement for a Before Insert Trigger.

DELIMITER $$
CREATE trigger before_students_insert
before insert
on students
FOR EACH ROW
BEGIN            		-- trigger body starts
INSERT INTO students_history
SET
id = new.id,
new_rank = new.rank;	 -- trigger body ends
END$$ 
DELIMITER );

When we make an insert into table students then the trigger inserts a new row in students_history table.

INSERT INTO students SET id = '7' , name = 'Sheldon', rank = '7'; 
mysql> SELECT * FROM students_history;
+------+----------+----------+
| id   | old_rank | new_rank |
+------+----------+----------+
|    7 |     NULL |        7 |
+------+----------+----------+

Note: We did not use the old keyword here as the MySQL Trigger.oldMap is only available for Update and Delete Triggers only.

If you use the old keyword here for old_rank= old.rank then MySQL will throw you the following error:

Error Code: 1363. There is no OLD row in on INSERT trigger.

After Insert Trigger

AFTER INSERT trigger will be fired after an INSERT operation is executed on the table. For instance, please find a sample Create Statement for an After Insert Trigger.

DELIMITER $$
CREATE trigger after_students_insert
after insert
on students
FOR EACH ROW
BEGIN            		-- trigger body starts
INSERT INTO students_history
SET
id = new.id,
new_rank = new.rank;	 -- trigger body ends
END$$ 
DELIMITER );

On making an insert into students table, after insert trigger inserts a new row in students_history table.

INSERT INTO students set id = '9' , name = 'Leonard', rank = '9';
SELECT * FROM students_history;
+------+----------+----------+
| id   | old_rank | new_rank |
+------+----------+----------+
|   9  |     NULL |       9  |
+------+----------+----------+

Before Delete Trigger

A BEFORE DELETE trigger will get fired before the DELETE operation is executed on the table with which the trigger is associated. We can use Before Delete trigger to perform an operation before deleting from a row from a table. For instance, please find a sample Create statement for a Before Delete Trigger.

DELIMITER $$
CREATE trigger before_students_delete
before delete
on students
FOR EACH ROW
BEGIN            		-- trigger body starts
INSERT INTO students_history
SET
id = old.id,
old_rank = old.rank; -- trigger body ends
END$$ 
DELIMITER );

Here we did not use new keyword as it does not make any sense while we have to delete the row and make no other changes to it.

On deleting any row from students table, before delete trigger inserts a new row in students_history table as shown below:

DELETE FROM students WHERE id = 1 LIMIT 1;
select * from students_history;
 +------+----------+----------+
 | id   | old_rank | new_rank |
 +------+----------+----------+
 |    1 |        1 |     NULL |
 +------+----------+----------+

After Delete Trigger

A DELETE operation triggers the After Delete Trigger on the table. We can use After Delete trigger to perform an operation after deleting a row from a table. For example, please find a sample After Delete Trigger Create statement.

DELIMITER $$
create trigger after_students_delete
after delete
on students
FOR EACH ROW
BEGIN            		-- trigger body starts
INSERT INTO students_history
SET
id = old.id,
old_rank = old.rank; -- trigger body ends
END$$ 
DELIMITER );

A delete statement on the students table activates the After Delete trigger which inserts a new row in students_history table.

DELETE FROM students WHERE id = 3 LIMIT 1;
SELECT * FROM students_history;
 +------+----------+----------+
 | id   | old_rank | new_rank |
 +------+----------+----------+
 |    3 |        3 |     NULL |
 +------+----------+----------+

Conclusion

In conclusion, we implemented AFTER UPDATE, AFTER INSERT, BEFORE INSERT, BEFORE DELETE, and AFTER DELETE triggers. We learned about the use cases of all the triggers. I hope you found this article helpful and informative. You can read more about MySQL triggers from here.

Kindly leave your valuable comments below to help me improve this resource.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *