Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we create multiple MySQL triggers for the same trigger event and action time?
MySQL 5.7.2+ allows us to create multiple triggers for the same event and action time in a table. Both the triggers will activate sequentially when the event occurs. It can be understood with the help of an example −
Example
In this example, we are creating multiple triggers for the same event say BEFORE UPDATE. The names of the triggers are ‘Studentdetail_before_update’ and ‘Studentdetail_before_update2’. They will activate sequentially when an event occurs. We are creating these triggers on the table ‘Student_detail’ having the following data −
mysql> Select * from Student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address | +-----------+-------------+------------+ | 100 | Gaurav | Delhi | | 101 | Raman | Shimla | | 103 | Rahul | Jaipur | | 104 | Ram | Chandigarh | | 105 | Mohan | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.06 sec) mysql> Delimiter //
Now with the help of the following query, we will create the first trigger, which will be created by the same query as earlier.
mysql> Create Trigger studentdetail_before_update -> BEFORE UPDATE -> ON Student_detail -> FOR EACH ROW -> BEGIN -> DECLARE AUSER Varchar(40); -> SELECT USER() into AUSER; ->INSERT INTO Student_detail_updated(studentid, Updated_date,Updated_by) values(OLD.studentid,NOW(),AUSER); -> END; // Query OK, 0 rows affected (0.17 sec) mysql> Update student_detail SET Address = 'Ludhiana' Where studentName = 'Ram'; Query OK, 1 row affected (0.15 sec) Rows matched: 1 Changed: 1 Warnings: 0
After invoking the above-created trigger we got the following result −
mysql> Select * from student_detail_updated; +-----------+---------------------+----------------+ | studentid | Updated_date | Updated_by | +-----------+---------------------+----------------+ | 104 | 2017-11-22 16:17:16 | root@localhost | +-----------+---------------------+----------------+ 1 row in set (0.00 sec)
Now, the second trigger of the same event and action time can be created as follows −
mysql> Create Trigger studentdetail_before_update2 -> BEFORE UPDATE -> ON Student_detail -> FOR EACH ROW FOLLOWS studentdetail_before_update -> BEGIN -> DECLARE AUSER Varchar(40); -> SELECT USER() into AUSER; -> INSERT INTO Student_detail_updated(studentid, Updated_date,Updated_by) values(OLD.studentid,NOW(),AUSER); -> END; // Query OK, 0 rows affected (0.15 sec)
The above trigger will activate after the first trigger because we are using the keyword ‘FOLLOWS’.
mysql> Update Student_detail SET Address = 'Patiala' WHERE studentname = 'Mohan'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0
Now, when we update the value, the following result set is showing two rows for the same event and action time. Second row represents the value after studentdetail_before_update trigger and third row represents the value after studentdetail_before_update2 trigger.
mysql> Select * from student_detail_updated; +-----------+---------------------+----------------+ | studentid | Updated_date | Updated_by | +-----------+---------------------+----------------+ | 104 | 2017-11-22 16:17:16 | root@localhost | | 105 | 2017-11-22 16:19:28 | root@localhost | | 105 | 2017-11-22 16:19:28 | root@localhost | +-----------+---------------------+----------------+ 3 rows in set (0.00 sec)
450 Views
