Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Fire Trigger for both Insert and Update

Tags:

mysql

triggers

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


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.


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


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!