Skipping a row operation from a trigger

One can signal a condition from inside a trigger to get the whole statement aborted. For example, with

SIGNAL foo;

or, as a more complex example,

SIGNAL SQLSTATE '23000'

	SET MYSQL_ERRNO=1062, MESSAGE_TEXT='Duplicate entry for key';

According to the SQL Standard, SQLSTATE values in the 02 class signal a “no data” condition. In a way, it makes sense if a “no data” signal in the BEFORE INSERT trigger would’ve caused the row not to be inserted. For example, with SQLSTATE “02TRG” or “02ROW”, an implementation-defined subclass within the standard 02 class.

This way a BEFORE can tell the server "skip this row operation, do not insert/update/delete this row" by doing SIGNAL SQLSTATE '02TRG'

SQLSTATE having the value “02TRG” has special treating only for BEFORE triggers; the value "02TRG" is case-sensitive, e.g. value "02trg" is not treated in any special way.

For BEFORE INSERT triggers, issuing the statement

SIGNAL SQLSTATE '02TRG'

forces to skip the values being inserted (either those specified explicitly by the clause VALUES, or that are fetched by a query of the statement

INSERT ... SELECT

) or loaded using the statement LOAD DATA INFILE/LOAD XML INFILE

For BEFORE UPDATE triggers, issuing the statement

SIGNAL SQLSTATE '02TRG'

forces to skip modification of the current row being process be the UPDATE statement. In case a multi-update statement is run, the trigger skips an update of the first table only (the one that has an associated trigger), the second table (without a trigger) is still updated.

For BEFORE DELETE triggers, issuing the statement

SIGNAL SQLSTATE '02TRG'

forces to skip the row of the table, that is the current row being processed is not deleted from a table

In case there is both a BEFORE and an AFTER trigger on the same event type (e.g. ON INSERT) and the BEFORE trigger issues the signal with SQLSTATE “02TRG” then in addition to skipping the row the AFTER trigger won't also be invoked.