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 TRIGGERstatement, however, Xpand will generate an error when the trigger is executed (i.e. on
NOT NULLcolumns within a
BEFOREtrigger do not retain the
NOT NULLproperty while within the trigger. In MySQL, a
NOT NULLcolumn set to
NULLwithin a trigger will immediately generate an error. In Xpand, however, the column will be set to
NULL. Unless changed to
NOT NULLlater in the trigger, an error will occur when the DML is executed.
NOT NULL AUTO_INCREMENTcolumns are an exception. In MySQL,
NOT NULL AUTO_INCREMENTcolumns set to
NULLwithin 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_INCREMENTcolumns with
NULLvalues will be silently converted to
0, allowing auto increment to work correctly.
Xpand does not support triggers that modify columns with the
ENUMdata type. No error message will be given when creating or executing triggers that modify
ENUMtypes, but the results are undefined. For example, if a table has a
state, then the effects of the statement
NEW.state = NEW.state + 1are undefined. Use explicit values instead, like
NEW.state = "RUNNING".
Xpand supports the
TRIGGERprivilege. In Xpand 6.0 and before, the
SUPERprivilege also grants all permissions granted by the
TRIGGERprivilege. Starting with Xpand 6.1, the
SUPERprivilege no longer includes the permissions granted by the
Query logs from
AFTERtriggers will appear before the logs for the statement invoking the trigger.