MySQL Fire Trigger for both Insert and Update

MysqlTriggers

Mysql Problem Overview


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.

Mysql Solutions


Solution 1 - Mysql

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

Solution 2 - Mysql

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.

Solution 3 - Mysql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAdam S-PriceView Question on Stackoverflow
Solution 1 - MysqlderobertView Answer on Stackoverflow
Solution 2 - MysqlAl ZziwaView Answer on Stackoverflow
Solution 3 - MysqlJeff_AlieffsonView Answer on Stackoverflow