CODEWITHSUNDEEP
mysqltriggersmariadb
bobby
bobby

Reputation: 455

How to use multiple events in one sql trigger?

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:

enter image description here

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

Upvotes: 5

Views: 4211

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

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.

Upvotes: 6

Related Questions