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.

derobert · Accepted Answer · 2009-08-23 10:20:56Z

162

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

answered Aug 23, 2009 at 10:20

derobert

51.4k15 gold badges98 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zxaos Over a year ago

Could you give a toy example of this for those of us who are unfamiliar with the syntax?

derobert Over a year ago

@Zxaos: I'd suggest starting with dev.mysql.com/doc/refman/5.1/en/create-procedure.html (which includes some examples) and asking your own question(s) if needed.

luismartingil Over a year ago

"strongly recommend not calling any stored procedures from a Trigger" # dba.stackexchange.com/questions/10657/…

derobert Over a year ago

@luismartingil there is probably some extra overhead of calling a procedure vs. inlining it. But in exchange you get easier maintenance and a guarantee that the two triggers' code won't accidentally diverge.

Al Zziwa · Accepted Answer · 2014-08-30 18:47:12Z

62

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.

edited Aug 30, 2014 at 18:47

answered Aug 30, 2014 at 18:38

Al Zziwa

1,1479 silver badges5 bronze badges

4 Comments

VaTo Over a year ago

What's IN table_row_id VARCHAR(255) in this case? I mean how are you defining which row is going to be inserted or updated?

ucbpaladin Over a year ago

Is there a reason you left in the begin / end statements in the triggers, since each trigger now executes only a single statement?

Al Zziwa Over a year ago

@VaTo the table_row_id is the way you know which row in the table was added or updated.

Al Zziwa Over a year ago

@ucbpaladin the // are left to ease reuse of the code if more stuff is added in the triggers.

Jeff_Alieffson · Accepted Answer · 2013-06-23 13:10:29Z

15

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

answered Jun 23, 2013 at 13:10

Jeff_Alieffson

2,8921 gold badge32 silver badges34 bronze badges

Collectives™ on Stack Overflow

MySQL Fire Trigger for both Insert and Update

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related