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.

score 158 · Accepted Answer · answered Aug 23 '09 at 10:20

158

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 '09 at 10:20

derobert

49,731
15
94
124

4

Could you give a toy example of this for those of us who are unfamiliar with the syntax? – Zxaos Jul 01 '11 at 00:00
4

@Zxaos: I'd suggest starting with http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html (which includes some examples) and asking your own question(s) if needed. – derobert Jul 01 '11 at 05:21
"strongly recommend not calling any stored procedures from a Trigger" # https://dba.stackexchange.com/questions/10657/call-a-stored-procedure-from-a-trigger – luismartingil Oct 09 '19 at 12:50
1

@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. – derobert Oct 09 '19 at 14:30

Al Zziwa · Answer 2 · 2014-08-30T18:47:12.567

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.

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? — VaTo, Feb 25 '16 at 01:04
Is there a reason you left in the begin / end statements in the triggers, since each trigger now executes only a single statement? — ucbpaladin, Jul 28 '22 at 22:10
@VaTo the table_row_id is the way you know which row in the table was added or updated. — Al Zziwa, Jul 30 '22 at 17:32
@ucbpaladin the // are left to ease reuse of the code if more stuff is added in the triggers. — Al Zziwa, Jul 30 '22 at 17:32

score 14 · Answer 3 · answered Jun 23 '13 at 13:10

14

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

answered Jun 23 '13 at 13:10

Jeff_Alieffson

2,672
29
34

MySQL Fire Trigger for both Insert and Update

3 Answers3

Linked

Related