MySQL Fire Trigger for both Insert and Update

Question

Is it possible to fire a mysql trigger for both the insert and update events of a table   I know I can do the following  CREATE TRIGGER my trigger     AFTER INSERT ON  table      FOR EACH ROW BEGIN       END     CREATE TRIGGER my trigger     AFTER UPDATE ON  table      FOR EACH ROW BEGIN       END      But how can I do  CREATE TRIGGER my trigger     AFTER INSERT ON  table  AND     AFTER UPDATE ON  table      FOR EACH ROW BEGIN         Is it possible  or do I have to use 2 triggers  The code is the same for both and I don t want to repeat it

User · Answer

unfortunately we can t use in MySQL after INSERT or UPDATE description  like in Oracle

User · Answer

In response to  Zxaos request  since we can not have AND OR operators for MySQL triggers  starting with your code  below is a complete example to achieve the same   1  Define the INSERT trigger    DELIMITER    DROP TRIGGER IF EXISTS my insert trigger   CREATE DEFINER root localhost TRIGGER my insert trigger     AFTER INSERT ON  table      FOR EACH ROW  BEGIN     -- Call the common procedure ran if there is an INSERT or UPDATE on  table      -- NEW id is an example parameter passed to the procedure but is not required     -- if you do not need to pass anything to your procedure      CALL procedure to run processes due to changes on table NEW id   END   DELIMITER     2  Define the UPDATE trigger   DELIMITER    DROP TRIGGER IF EXISTS my update trigger    CREATE DEFINER root localhost TRIGGER my update trigger     AFTER UPDATE ON  table      FOR EACH ROW BEGIN     -- Call the common procedure ran if there is an INSERT or UPDATE on  table      CALL procedure to run processes due to changes on table NEW id   END   DELIMITER     3  Define the common PROCEDURE used by both these triggers    DELIMITER    DROP PROCEDURE IF EXISTS procedure to run processes due to changes on table    CREATE DEFINER root localhost PROCEDURE procedure to run processes due to changes on table IN table row id VARCHAR 255   READS SQL DATA BEGIN      -- Write your MySQL code to perform when a  table  row is inserted or updated here  END   DELIMITER     You note that I take care to restore the delimiter when I am done with my business defining the triggers and procedure

User · Answer

You have to create two triggers  but you can move the common code into a procedure and have them both call the procedure

[mysql] MySQL Fire Trigger for both Insert and Update

Examples related to mysql

Examples related to triggers