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.

Answer 1

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

Answer 2

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.

Answer 3

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