Mariadb Database level trigger

Question

CREATE TRIGGER safety_drop
ON DATABASE 
FOR DROP_TABLE
AS
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETING NOT ALLOWED';
ROLLBACK;
END

ERROR

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON DATABASE FOR DROP_TABLE AS BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEX' at line 2

score 0 · Accepted Answer · answered Jul 26 '20 at 22:31

MariaDB just does not support "database-level" triggers. Quoting the documentation (emphasis added):

A trigger, as its name suggests, is a set of statements that run, or are triggered, when an event occurs on a table. [...] The event can be an INSERT, an UPDATE or a DELETE.

So basically this works on DML operations (statements that change the data), not on DDL (operations that change the structure).

As for what you ask for, using permissions and roles to prevent users from dropping any table in the database seems like a more relevant approach.

Actually I want to prevent some tables to drop when I will down migration. Like on migration down I don't want to delete data off countries table. — Talha Jamshed, Jul 26 '20 at 23:05

Mariadb Database level trigger

1 Answers1