CodeShark CodeShark - 1 month ago 12
SQL Question

MariaDB: syntax error in Referencing old row

I'm trying to execute a very basic trigger I've found in MariaDB's Trigger examples:

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books /* See note (a) */
REFERENCING OLD ROW AS Old /* See note (b) */
FOR EACH ROW /* See note (c) */
INSERT INTO Books_Deleted_Log
VALUES (Old.title); /* See note (d) */


This doesn't work because I've a syntax error in my query on line 3:
REFERENCING OLD ROW ...
. In general I'm not able to run any example I find for MariaDB or MySQL. So what is wrong here? I read that the
REFERENCING
-part is not necessary but it won't work without it too. Any trigger query I do is a SQL syntax error. Could this be related to using DBeaver as SQL-Client?

Answer

I would expect the trigger to look like this:

DELIMITER $$

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books
FOR EACH ROW 
BEGIN                         /* See note (c) */
    INSERT INTO Books_Deleted_Log(title)
        VALUES (Old.title); 
END;$$

DELIMITER ;

REFERENCING is not part of MySQL syntax but is part of MariaDB syntax.