Triggers

Overview

A trigger performs a series of instructions whenever a specified event occurs for a table. Possible triggering events are INSERT, UPDATE, and DELETE. A trigger can be set to be executed either BEFORE or AFTER an event. For example, a trigger might be run AFTER INSERT or BEFORE DELETE.

Information on this topic can be found in the MariaDB Public KB.

The Xpand implementation for triggers is intended to provide compatibility with the MySQL implementation of triggers. For general semantics and operation of triggers refer to the MySQL documentation for triggers.

Caveats for triggers

  • Xpand does not perform create-time checks on triggers. If a trigger is defined with access errors (permissions, modifying table the trigger is defined on, etc.), MySQL will generate an error on the CREATE TRIGGER statement, however, Xpand will generate an error when the trigger is executed (i.e. on INSERT/UPDATE/DELETE).

  • NOT NULL columns within a BEFORE trigger do not retain the NOT NULL property while within the trigger. In MySQL, a NOT NULL column set to NULL within a trigger will immediately generate an error. In Xpand, however, the column will be set to NULL. Unless changed to NOT NULL later in the trigger, an error will occur when the DML is executed.

    • NOT NULL AUTO_INCREMENT columns are an exception. In MySQL, NOT NULL AUTO_INCREMENT columns set to NULL within a trigger are immediately and silently converted to 0. In Xpand the column will be set to NULL. After a trigger finishes executing, but before the subsequent DML operation, any NOT NULL AUTO_INCREMENT columns with NULL values will be silently converted to 0, allowing auto increment to work correctly.

  • Xpand does not support triggers that modify columns with the ENUM data type. No error message will be given when creating or executing triggers that modify ENUM types, but the results are undefined. For example, if a table has a ENUM column state, then the effects of the statement NEW.state = NEW.state + 1 are undefined. Use explicit values instead, like NEW.state = "RUNNING".

  • Xpand supports the TRIGGER privilege. In Xpand 6.0 and before, the SUPER privilege also grants all permissions granted by the TRIGGER privilege. Starting with Xpand 6.1, the SUPER privilege no longer includes the permissions granted by the TRIGGER privilege.

  • Query logs from BEFORE and AFTER triggers will appear before the logs for the statement invoking the trigger.