How to use multiple events in one sql trigger?

Question

This is my code at the moment:

DROP TRIGGER `backup`;

DELIMITER $$
CREATE TRIGGER `backup` AFTER INSERT UPDATE DELETE
    ON `warehouse`
    FOR EACH ROW 
        BEGIN

        END$$
DELIMITER ;

This is the error I keep getting:

I checked my version of MariaDB. It's 10.1.21

It works if I use only one event, but with two or three it throws this error.

Insert Update trigger how to determine if insert or update

you must made three separated triggers for INSERT, UPDATE and DELETE — Raymond Nijland, Sep 02 '17 at 20:03
is there any way I could do it all in one? i found some examples, the syntax look like this above — bobby, Sep 02 '17 at 20:08
That link points to a question about SQL Server, which is quite a different animal than MySQL. Be careful that you limit research to the same RDBMS engine; there are many differences. (MySQL and MaraiDB are virtually identical to each other.) — Rick James, Sep 02 '17 at 20:48

Bill Karwin · Accepted Answer · 2017-09-02T20:41:23.840

In MySQL or MariaDB, each trigger must be defined for exactly one event. You cannot define a trigger that will work for multiple events.

https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html has the syntax:

trigger_event: { INSERT | UPDATE | DELETE }

This syntax notation means the event must be one of the three values INSERT, UPDATE, or DELETE.

Another clue is found if we DESCRIBE INFORMATION_SCHEMA.TRIGGERS:

EVENT_MANIPULATION enum('INSERT','UPDATE','DELETE')

The event type is an enum which means it can only have one value, not multiple.

The example you linked to is for Microsoft SQL Server, not MySQL or MariaDB.

Despite the fact that both "Microsoft" and "MySQL" start with a similar syllable, these are two different products, with different features.

How to use multiple events in one sql trigger?

1 Answers1