AFTER ... BEFORE COMMIT [OR SAVEPOINT] and AFTER ... FOR EACH STATEMENT triggers

A summary row maintained by FOR EACH ROW triggers is recomputed and rewritten once per changed child row. A transaction that changes K child rows of one parent writes the parent K times, and only the last write is ever observed. The other K-1 writes cost redo, undo, row locks held longer, binary log volume and K-1 re-aggregations.

This adds two trigger granularities that fire once over a set of changed rows instead of once per row:

CREATE [OR REPLACE] [DEFINER=...] TRIGGER [IF NOT EXISTS]

    trigger_name AFTER {trigger_event [OR trigger_event] ...}

    ON tbl_name { BEFORE COMMIT [OR SAVEPOINT] | FOR EACH STATEMENT }

    [{OF | EXCEPT} col_name [, col_name] ...]

    [{FOLLOWS|PRECEDES} other_trigger_name]

    trigger_stmt

trigger_event: INSERT | UPDATE | DELETE

• BEFORE COMMIT fires at the commit boundary of the transaction, over the rows the transaction changed in the table. OR SAVEPOINT makes it fire at a SAVEPOINT as well.
• FOR EACH STATEMENT fires once at the end of a DML statement, over the rows that statement matched in the table.

The clause takes the place of FOR EACH ROW, so the leading AFTER trigger_event ON tbl_name is the same for all three granularities. Both read the changed rows through MODIFIED (see MODIFIED under Rules common to both granularities).

BEFORE COMMIT triggers

When they fire

• BEFORE COMMIT fires at the commit boundary of the transaction, over the rows the transaction changed in the table. It fires after the DML and before the transaction is made durable, while the transaction is still active, so the body's writes join the same transaction and commit atomically with it.
• The commit boundaries are COMMIT, an implicit commit, the end of an autocommit statement and XA END.
• BEFORE COMMIT triggers fire at one event, the commit, whatever their INSERT/UPDATE/DELETE events are.
• They also fire when a statement since their previous firing activated one of their events on the table without changing a row (see Events a statement activates and rows it captures).

OR SAVEPOINT

• OR SAVEPOINT makes a BEFORE COMMIT trigger also fire before a SAVEPOINT executed when no statement is in progress, over the rows changed since its previous firing. Its results then become visible to the rest of the transaction. Without OR SAVEPOINT a savepoint only records a boundary; ROLLBACK TO SAVEPOINT drops the rows changed after it either way.
• A SAVEPOINT fires OR SAVEPOINT triggers when no statement is in progress: when the client sends it, or when a stored procedure that the client called (directly or through other procedures) executes it.
• One executed while a statement is running, inside a trigger body, a stored function, or a procedure called from either, only records its boundary, and the rows stay for the next SAVEPOINT that fires or for COMMIT.

Order of firing

• All BEFORE COMMIT triggers of a table form one list in creation order, and FOLLOWS/PRECEDES may name any other BEFORE COMMIT trigger of the table. INFORMATION_SCHEMA.TRIGGERS and SHOW TRIGGERS list them in the order of that list, and mysqldump creates them in the order it gets them, so a restore keeps it.
• At a boundary the tables fire in the order in which the transaction first changed them or activated one of their events.
• When the triggers of a table have run, every table their bodies wrote, and whose triggers fire at that boundary, fires right away, nested under it, before the next table. A table that has already fired fires again, over only the rows written since.
• Each captured row reaches each trigger of its table exactly once whatever the order, but a body that writes once per firing rather than once per row writes once for every firing.

What a body may do

• A BEFORE COMMIT trigger body is subject to the rules ROW trigger bodies follow today. It may write other tables, and the rows it writes are captured like any others, so the BEFORE COMMIT triggers of those tables fire for them and a roll-up can span several levels (child to parent to grandparent).
• It may not write a table on the current chain of firings, that is its own table or a table whose triggers led to its firing: that fails with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, as a ROW trigger body does when it writes a table the calling statement uses. A trigger therefore never fires itself again, and every chain of firings ends. The rule covers every statement under the body, in the routines it calls too; reading a table on the chain is allowed.
  • The SQL standard does not restrict the tables an AFTER trigger's action writes, so an AFTER trigger may fire itself there. It refuses only schema, dynamic and session statements in a triggered action (SQL:2011 draft 15.19 GR 5f) and writes to a table modified through a data change delta table (GR 5h); only a BEFORE trigger may not change data at all (GR 5g).
  • This rule, and the ROW rule that applies to FOR EACH STATEMENT bodies (see FOR EACH STATEMENT triggers, What a body may do), are deviations; the BEFORE COMMIT rule is built by analogy with the ROW one.
• It may not commit or roll back (ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG), by analogy with the SQL standard, which refuses both inside the atomic statement that fires a trigger (SQL:2011 draft 17.7 GR 2, 17.8 GR 2a); at a commit boundary no atomic statement fires it. The standard raises invalid transaction termination, class 2D, where MariaDB returns HY000, a deviation shared with ROW triggers.
• It may not set, roll back to or release a savepoint either, with ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG: such a statement written in the body is refused at CREATE TRIGGER, and one in a routine the body calls is refused when it runs. The savepoint rule covers every statement under the body: the routines it calls and the ROW and FOR EACH STATEMENT triggers its statements fire.
• One BEFORE COMMIT firing covers statements of every kind, so a BEFORE COMMIT body that uses INSERTING, UPDATING or DELETING is refused at CREATE TRIGGER with ER_NOT_ALLOWED_IN_THIS_CONTEXT.

When a body fails

At a commit (COMMIT, an implicit commit, or the end of an autocommit statement):

• A body that fails rolls back the whole transaction, as the SQL standard has it for a COMMIT whose triggered statements fail (SQL:1999 16.6 GR 6b, transaction rollback with subclass 004, triggered action exception). The SQL:2011 draft keeps the rollback for any error that prevents the commit, with an implementation-defined subclass (17.7 GR 6b). In the standard, the statement that runs at COMMIT and can fire triggers is the implicit DELETE of a temporary table declared ON COMMIT DELETE ROWS (16.6 GR 4), so BEFORE COMMIT triggers follow that rule by analogy.
• The statement fails with the new error ER_TRG_BEFORE_COMMIT_ROLLBACK, SQLSTATE 40004, which names the trigger whose body failed, and the body's error stays as a further condition of the statement.
• A body error that already reports a transaction rollback, a deadlock (ER_LOCK_DEADLOCK, SQLSTATE 40001), is the error of the statement instead, so that clients and replicas recognize it and retry the transaction. A replica also retries after 40004 when the body's error is one of slave_transaction_retry_errors, such as a lock wait timeout.
• A failure at a commit boundary that is not a body's, such as a KILL QUERY while the boundary re-opens a table, also rolls back the whole transaction, as the SQL standard has it for any error that prevents the commit (SQL:1999 16.6 GR 6c), and fails the statement with the new error ER_TRG_BEFORE_COMMIT_NOT_FIRED, SQLSTATE 40000, which names the table; the failure stays as a further condition.
• The body's error is condition number 1, ahead of 40004, as the error of a failing ROW trigger is; SQL:1999 10.12 GR 4b enters it in a condition other than condition number 1, a deviation shared with ROW triggers (see FOR EACH STATEMENT triggers, When a statement or a body fails). SQL:1999 19.1 GR 2 also makes condition number 1 the condition of the SQLSTATE the statement returns. MariaDB lists the conditions of every statement in the order they were raised, so an INSERT that fails with SQLSTATE 23000 can have a warning as condition number 1; 40004 and 40000 follow that general deviation.
• The statement that caused the commit fails:
  • COMMIT does not commit;
  • a DDL statement that commits implicitly does not run;
  • BEGIN, LOCK TABLES and UNLOCK TABLES leave LOCK TABLES in place;
  • SET autocommit=1 leaves autocommit off;
  • at the end of an autocommit statement the statement has already run, and it is rolled back with the transaction.

At XA END:

• A failure rolls the branch back and leaves it ROLLBACK ONLY, as a deadlock does, and the branch accepts only XA ROLLBACK. XA END fails with the XA_RB error of the cause, with the cause as a further condition:
  • ER_XA_RBDEADLOCK for a deadlock in a body;
  • ER_XA_RBTIMEOUT for a lock wait timeout;
  • ER_XA_RBROLLBACK otherwise.

At a SAVEPOINT:

• A body that fails fails the SAVEPOINT with the body's own error, as a failing ROW trigger fails its DML statement. Everything the bodies did at that boundary is undone, SAVEPOINT sets no savepoint, and the transaction stays active.
• The exception is a body error after which the engine rolls back the whole transaction, as it does after any statement: a deadlock, or a lock wait timeout with innodb_rollback_on_timeout.
• The same holds for a SAVEPOINT that cannot be set after its triggers fired, such as one in a transaction that wrote to an engine without savepoints: it fails with that error, and what the bodies did is undone.
• Retrying the SAVEPOINT fires every trigger again over the same rows.

Tables they are allowed on

• BEFORE COMMIT requires a transactional table: CREATE TRIGGER on a table whose engine cannot roll back fails with ER_NOT_SUPPORTED_YET, and so does ALTER TABLE ... ENGINE= to such an engine while the table has a BEFORE COMMIT trigger.
• CREATE TABLE ... SELECT that writes a table with BEFORE COMMIT triggers on the event, through a stored function or a trigger body, is refused with ER_NOT_SUPPORTED_YET before the new table is created, whatever the engine of the new table. CREATE TABLE ... SELECT commits in the middle of the statement, and when it fails, InnoDB drops the new table by committing the transaction, which would commit the rows the statement wrote without the triggers firing for them. CREATE TEMPORARY TABLE ... SELECT commits nothing and is allowed.

The accumulator

• The changed rows are kept in one accumulator per (transaction, table), shared by all BEFORE COMMIT triggers of the table. The sketch keeps the accumulators of all tables of a transaction in one sequence, THD::st_transactions::trg_commit, with one entry per table; a position in it is a captured row or an activation.
• It is created by the table's first captured row or activation, with the union of the columns those triggers capture plus the control columns, and every captured row carries all of them. An activation takes a place in the accumulator and holds no row.
• The column set never changes during the accumulator's life: the transaction holds the table's metadata lock from its creation, so no trigger of the table can be created or dropped.
• A rollback (to a savepoint, or of a failed statement) to a point before the table's first captured row or activation removes the accumulator; the next row or activation creates it again from the triggers the table has then.

FOR EACH STATEMENT triggers

When they fire

• FOR EACH STATEMENT fires once at the end of a DML statement, over the rows that statement matched in the table (SQL:2011 draft 15.19 GR 3b), also when it matched none (see Events a statement activates and rows it captures).
• A statement inside a trigger body or a stored routine is a statement of its own: its rows fire its triggers at its end, not with the statement that called it (13.4 GR 2-3, which 15.19 GR 5d applies to each statement of a triggered action).
• A prepared statement executed with an array of parameter sets fires them once for INSERT and REPLACE, which run all the sets as one statement, and once per set for UPDATE and DELETE, which run as one statement per set.
• Rows are collected per table, not per reference to the table. A statement that writes a table through several aliases or views (a self-join UPDATE or DELETE) fires each of the table's FOR EACH STATEMENT triggers once, over the rows written through all of them, in the order they were written. The SQL standard adds such rows to the state change that already exists for the table and event (SQL:2011 draft 15.13 GR 6b i for UPDATE, 15.7 GR 5a for DELETE). A row written through two aliases is captured twice.
• The rows of a FOR EACH STATEMENT trigger are kept the same way as in the accumulator (see BEFORE COMMIT triggers, The accumulator), in a statement set per (statement, table), which also records the events the statement activated on the table.

Order of firing

• FOR EACH STATEMENT triggers are ordered like ROW triggers: they have per-event lists of their own, built, numbered in ACTION_ORDER and stored in the .TRG file the same way, and the anchor of FOLLOWS/PRECEDES must be a FOR EACH STATEMENT trigger that has every event of the trigger being created.
• A statement that activates one event fires that event's list in ROW order. A statement that activates several, such as REPLACE (INSERT and DELETE) or INSERT ... ON DUPLICATE KEY UPDATE (INSERT and UPDATE), walks the lists of its events in INSERT, UPDATE, DELETE order and fires each trigger once, at the first list it is on.
• Their order relative to a multi-event trigger depends on MDEV-37711.
• Multi-event triggers are a MariaDB extension; the SQL standard gives a trigger one event (SQL:2011 draft 11.49) and runs a statement-level trigger once per state change, which is per event (15.19 GR 3b, 4.39.2). A multi-event FOR EACH STATEMENT trigger fires once per statement, over the rows of all its events, which MODIFIED tells apart by __op.
• FOR EACH STATEMENT triggers fire after every AFTER ROW trigger of their statement, because ROW triggers fire during the statement, one row at a time. The SQL standard runs all activated AFTER triggers, row-level and statement-level, at the end of the statement in creation order (SQL:2011 draft 4.39.1, 13.4 GR 15b, 15.18 SR 2-3 and GR 2); this deviation follows from the existing ROW behavior, and PostgreSQL has it too.
• A statement that writes several tables fires their FOR EACH STATEMENT triggers table by table, in the order in which it first reached each table, which for a multi-table UPDATE or DELETE is the join order the optimizer chose. The SQL standard orders the AFTER triggers of all the statement's tables by creation time (same clauses), and runs each row-level trigger over all its rows before the next trigger (15.19 GR 3a). AFTER ROW triggers deviate from both: they fire during the statement, all of them for one row before the next row, and the tables' first firings come in the order the join reaches them. The order across tables is that deviation, shared with ROW triggers.

INSERTING, UPDATING and DELETING

• In a FOR EACH STATEMENT body INSERTING, UPDATING and DELETING tell which statement fired the trigger, as they do in a statement-level trigger in Oracle, whatever events the table's triggers are on:
  • INSERTING for INSERT, INSERT ... SELECT, LOAD DATA, REPLACE and INSERT ... ON DUPLICATE KEY UPDATE;
  • UPDATING for UPDATE and DELETING for DELETE, also when FOR PORTION OF adds INSERT.
• A replica applying row events knows no statement: there they name the first of INSERT, UPDATE and DELETE whose rows the table's FOR EACH STATEMENT triggers captured (see Replication).
• In a BEFORE COMMIT body they are refused (see BEFORE COMMIT triggers, What a body may do).

What a body may do

• A FOR EACH STATEMENT body follows the ROW rule: writing a table the triggering statement uses, its own table included, fails with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG.
• Outside a BEFORE COMMIT body, a FOR EACH STATEMENT body may use savepoints, as a ROW body may, at a savepoint level of its own, as the SQL standard gives every triggered action (SQL:2011 draft 15.19 GR 5c, 5i).
• BEFORE FOR EACH STATEMENT is refused with ER_NOT_SUPPORTED_YET. The SQL standard has it (statement-level triggers are Feature T212, SQL:2011 draft 11.49 CR 2), without transition tables (11.49 SR 14b).

When a statement or a body fails

• A statement that fails fires no FOR EACH STATEMENT trigger, as the SQL standard has it (SQL:2011 draft 13.4 GR 15: AFTER triggers run only if no exception has been raised). The standard also cancels the changes of the failed statement (13.4 GR 17b). On a non-transactional table MariaDB cannot, so the rows written before the error stay: the AFTER ROW triggers have fired for them, but no FOR EACH STATEMENT trigger ever sees them.
• Inside a stored function or a trigger body, a failed statement whose error a handler catches is not rolled back on any table, as for ROW triggers today: its rows stay, its FOR EACH STATEMENT triggers do not fire, and the BEFORE COMMIT triggers of a transactional table fire over them at the commit.
• A failing FOR EACH STATEMENT body fails its statement, as a failing AFTER ROW trigger does (4.39.2, 15.19 GR 5l). The statement reports the body's own error, as it does for a ROW trigger. The SQL standard raises a triggered action exception (SQL:2011 draft 15.19 GR 5l; spelled "trigger action exception" in SQL:1999 10.12 GR 4b), and SQL:1999 enters the body's error in a condition other than condition number 1, a deviation shared with ROW triggers.

Rules common to both granularities

Events a statement activates and rows it captures

• A statement fires the FOR EACH STATEMENT triggers of every event it activates on a table it writes, whether or not a row changes. This is the SQL standard's rule: a searched UPDATE or DELETE that matches no row still runs its statement-level triggers over an empty state change (SQL:2011 draft 14.9 GR 8 and 15.7 GR 5 for DELETE, 14.14 GR 14-15 and 15.13 GR 6b for UPDATE, 14.11 GR 8-9 and 15.10 GR 3 for INSERT, 15.19 GR 3b; SQL:1999 14.7 GR 7-8 and 14.14 GR 4 for DELETE, 14.10 GR 15-17 for UPDATE, 10.12 GR 2b).
• BEFORE COMMIT triggers, with or without OR SAVEPOINT, fire at a boundary when a statement activated one of their events on the table since their previous firing, even if no row changed; their MODIFIED then holds no row of that statement.
• The events a statement activates, whether or not a row conflicts:
  • INSERT, UPDATE and DELETE activate their own event;
  • REPLACE and LOAD DATA ... REPLACE activate INSERT and DELETE;
  • INSERT ... ON DUPLICATE KEY UPDATE activates INSERT and UPDATE;
  • FOR PORTION OF adds INSERT.
• This extends the rule the standard gives MERGE, which activates every action it specifies whether or not it occurs (SQL:2011 draft 14.12 GR 6).
• The tables a statement activates its events on:
  • a multi-table UPDATE or DELETE activates its event on the tables it writes, not on those it only reads;
  • an INSERT, REPLACE or LOAD DATA activates its events on the one table it writes, also through a view of several tables, and not on the tables its SELECT reads, even under FOR UPDATE.
• Activates nothing:
  • EXPLAIN;
  • DELETE HISTORY, as it fires no ROW trigger;
  • TRUNCATE TABLE, which the SQL standard defines as deleting all rows without causing any triggered action (SQL:2011 draft 14.10);
  • reading a table.
• An activation is rolled back like a row: with its failed statement, or by ROLLBACK TO SAVEPOINT.
• A row reaches FOR EACH STATEMENT and BEFORE COMMIT triggers when the statement matches it, as it reaches an AFTER ROW trigger.
• A matched row that the statement leaves unchanged, such as with UPDATE t SET a= a or an INSERT ... ON DUPLICATE KEY UPDATE with the same values, is captured, and its OLD and NEW rows in MODIFIED are equal: the standard makes every row identified for replacement a transition, changed or not (SQL:2011 draft 14.14 GR 13, 15.13 GR 6b).

MODIFIED

• The body reads the changed rows through MODIFIED, a read-only quasi-table of the trigger, provided through an API.
• MODIFIED takes the place of the old and new transition tables that a trigger of the SQL standard names with REFERENCING OLD TABLE and NEW TABLE (SQL:2011 draft 11.49), as the fixed NEW and OLD of a ROW trigger take the place of REFERENCING OLD ROW and NEW ROW: one quasi-table of a fixed name holds both images, told apart by __state.
• Why a quasi-table. A BEFORE COMMIT trigger does batch processing. It typically groups the changed rows by a column, such as the parent key of a roll-up, and computes aggregates over each group, either directly in a query over MODIFIED or by iterating over the groups with a cursor. A quasi-table makes that ordinary SQL: GROUP BY, ORDER BY, joins and cursors work on MODIFIED as on any table. An interface that presents the changed rows one at a time, with a NEXT step and INSERTING, UPDATING, DELETING, NEW and OLD describing the current row, would leave the grouping and ordering to the body, which makes them extremely challenging to write.
• In a BEFORE COMMIT trigger it proxies into the table's accumulator (see BEFORE COMMIT triggers, The accumulator) and holds the rows changed in the table within this transaction that an earlier firing of the same trigger has not processed.
• In a FOR EACH STATEMENT trigger it holds the rows that the firing statement matched in the table, as the standard's transition tables of a statement-level trigger hold the transitions of its state change (SQL:2011 draft 15.19 GR 4a), and it exists only while that firing runs.
• In both, MODIFIED holds only rows of the trigger's own events and only the columns the trigger captures. A statement that changed no row adds no row to it, so it is empty when no statement since the trigger's previous firing changed a row of its events.

Every row of MODIFIED also has control columns, which OF and EXCEPT never name:

• __op_order: the order of the change. The OLD and NEW rows of one UPDATE share it, so __op_order pairs them even when the update changed the primary key.
• __op: the operation, I, U or D.
• __state: the image, O for an OLD row or N for a NEW row. An UPDATE gives two rows, (U, O) and (U, N), with the same __op_order.

For example, a roll-up of child.amount into parent.total applies one change per parent at the commit, however many child rows the transaction changed. An UPDATE that moves a child row to another parent counts against the old parent through its OLD row and for the new one through its NEW row:

CREATE TRIGGER child_rollup AFTER INSERT OR UPDATE OR DELETE ON child

  BEFORE COMMIT OF parent_id, amount

  UPDATE parent p

    JOIN (SELECT parent_id,

                 SUM(IF(__state = 'N', amount, -amount)) AS delta

            FROM MODIFIED

           GROUP BY parent_id) m ON m.parent_id = p.id

    SET p.total = p.total + m.delta;

Capture lists

• OF col, ... after the clause captures only the listed columns, and EXCEPT col, ... captures all columns but the listed ones. The OF after FOR EACH STATEMENT or BEFORE COMMIT chooses the columns MODIFIED holds; it does not decide whether the trigger fires.
• Without either, the trigger captures every column that NEW/OLD reach in a ROW trigger, invisible and virtual columns included. This keeps large columns such as BLOB columns out of the captured rows when the trigger does not need them. The primary key may be left out.
• At CREATE TRIGGER, a listed column that does not exist is ER_BAD_FIELD_ERROR, a captured column named like a control column of MODIFIED is ER_DUP_FIELDNAME, and an empty set (an EXCEPT that names every column) is ER_TABLE_MUST_HAVE_COLUMNS. A column listed twice counts once.
• ALTER TABLE does not check the lists, just as it does not check NEW.col/OLD.col in ROW triggers today. A statement that creates the table's statement set entry or accumulator entry, by its first captured row or activation, checks the lists of all the table's triggers of that granularity, whatever their events, and fails with the same errors, e.g. ER_BAD_FIELD_ERROR ("Unknown column 'b' in 'EXCEPT'") after the column was dropped.

Restrictions

• Both are AFTER only and have no NEW/OLD row (ER_TRG_NO_SUCH_ROW_IN_TRG), which the SQL standard allows only in a row-level trigger (SQL:1999 11.38 SR 9).
• The firing filter UPDATE OF of the SQL standard, which activates an UPDATE trigger only when the statement sets one of the listed columns (SQL:2011 draft 4.39.2, 15.13 GR 6b), is refused with FOR EACH STATEMENT and BEFORE COMMIT (ER_NOT_SUPPORTED_YET): an UPDATE trigger captures every row the statement matches, whatever columns it sets. It still fires once per statement or transaction, the capture list keeps the columns it does not need out of the captured rows, and the body tells the changed columns from the unchanged ones by comparing the OLD and NEW rows in MODIFIED.
• An engine that updates or deletes directly (HA_CAN_DIRECT_UPDATE_AND_DELETE, e.g. Spider) bypasses the per-row trigger calls where rows are captured, so a FOR EACH STATEMENT or BEFORE COMMIT trigger on UPDATE switches direct update off for its table, and one on DELETE switches direct delete off, whatever the engine. Triggers on other events leave them on.

Privileges and execution context

• A FOR EACH STATEMENT or BEFORE COMMIT body runs as the trigger's DEFINER, as a ROW body does; the SQL standard runs it as the owner of the trigger's schema (SQL:2011 draft 4.39.1, 15.19 GR 5b), which MariaDB, having no schema owners, represents by the DEFINER.
• The DEFINER's TRIGGER privilege on the table is checked when it fires, so a REVOKE between the DML and the COMMIT makes the COMMIT fail.
• Reading a column of MODIFIED needs the DEFINER's SELECT privilege on it, as NEW.col and OLD.col do. The SQL standard checks SELECT on the table when the trigger is created, if its action names a transition table or variable (SQL:2011 draft 11.49 AR 3); MariaDB checks NEW, OLD and MODIFIED per column when the body reads them.
• The body runs as a sub-statement, like a ROW body: it does not change LAST_INSERT_ID(), ROW_COUNT() or FOUND_ROWS() of the statement that reached the boundary, and it cannot return a result set.
• Session variables such as sql_mode and time_zone are read when the body runs, as they are for an explicit CALL made at that point.

Replication

BEFORE COMMIT:

• A replica applying row events with slave_run_triggers_for_rbr also captures rows for BEFORE COMMIT triggers and fires them when it commits the replicated transaction. It records no activation, so they fire only over rows that arrive.
• A body that fails rolls the replicated transaction back, and the replica retries it when the failure is one of slave_transaction_retry_errors (see BEFORE COMMIT triggers, When a body fails); otherwise the applier stops.

FOR EACH STATEMENT:

• With statement-based replication the replica executes the statement and fires its FOR EACH STATEMENT triggers itself, as it fires ROW triggers.
• On a replica with slave_run_triggers_for_rbr, the rows of one replicated statement arrive in row events that each hold rows of one table and one kind of change; one table's rows may span several of them, interleaved with other tables' events. FOR EACH STATEMENT triggers fire once per table after the last event of the statement, the one flagged STMT_END_F, has been applied.
• Without slave_run_triggers_for_rbr the replica fires no triggers, and the writes of the primary's trigger bodies arrive as rows of their own.
• A statement that changes no row writes no row event, so a replica applying row events fires no trigger for it.
• The replica fires only the events whose rows arrive: a REPLACE that replaces no row fires the INSERT and DELETE triggers on the primary and only the INSERT triggers on the replica.
• A matched row that the statement leaves unchanged is not written to the row events either, so the replica captures only the rows that changed, where the primary captures every matched row; an AFTER ROW trigger on the replica misses the same rows.
• The rows that the statements of one trigger body or stored function write to a table arrive in the same row events as the rest of the replicated statement's rows for that table, so on the replica a FOR EACH STATEMENT trigger on that table fires once for all of them, where the primary fires it once per statement.

Both:

• With YES or LOGGING, the replica runs no trigger on a table that had any trigger on the primary, whatever its kind and events, the same rule that applies to ROW triggers; with ENFORCE it runs them regardless.

Metadata

• INFORMATION_SCHEMA.TRIGGERS.ACTION_ORIENTATION shows the clause that follows ON tbl_name: ROW, STATEMENT, BEFORE COMMIT or BEFORE COMMIT OR SAVEPOINT. SHOW TRIGGERS shows the same value in a new Orientation column, added after all existing columns.
• BEFORE COMMIT and BEFORE COMMIT OR SAVEPOINT are an extension: SQL:1999 21.49 allows only ROW and STATEMENT in that column, so a client that expects only those meets new values.
• ACTION_ORDER numbers a BEFORE COMMIT trigger by its place in the table's single list, and a FOR EACH STATEMENT trigger within the per-event lists of its own granularity (see Order of firing in each section).
• ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW are NULL for FOR EACH STATEMENT and BEFORE COMMIT triggers, which have no row variables, and stay OLD and NEW for ROW triggers; both columns become nullable.
• INFORMATION_SCHEMA.TRIGGERED_CAPTURE_COLUMNS, a sibling of TRIGGERED_UPDATE_COLUMNS with the same columns, has one row for every column a FOR EACH STATEMENT or BEFORE COMMIT trigger captures: the result of evaluating its OF or EXCEPT list against the table's current definition, or every column when there is no list. It shows a column only to a user who has the INSERT or UPDATE privilege on it or on the table, as TRIGGERED_UPDATE_COLUMNS does. A list that no longer resolves after ALTER TABLE gives a warning and no rows for that trigger.

Stored definition and downgrade

• The granularity, OR SAVEPOINT and the capture list are stored only in the CREATE TRIGGER text in the .TRG file, as the UPDATE OF list is today, and are parsed again whenever the table is opened.
• A server that predates them cannot parse that text. After a downgrade it marks the table's triggers as unparseable, and every statement that writes a row of the table fails with ER_PARSE_ERROR. It cannot drop such a trigger either: its parser stops before it reads the trigger's name, which DROP TRIGGER finds the trigger by. These triggers have to be dropped before a downgrade.

Hookup sketch

The first PR does not execute trigger bodies. It parses the syntax, creates and stores the trigger objects, and wires every hook point. Where the real code does something the sketch cannot do yet (run a body, refuse a statement inside a body, undo what the bodies wrote), a named stub function sits at the call site, with the arguments the real code needs and a comment describing what it does. A stub that would refuse something logs what it would refuse and lets the statement continue.

Each hook writes one line to the error log describing what it would do and what it has in hand. main.trigger_commit_hookup prints those lines after each statement or group of statements, so the result file shows which hooks every statement reaches. rpl.rpl_row_statement_triggers does the same on a replica, main.trigger_statement_array_binding for a prepared statement executed with an array of parameter sets, main.trigger_commit_refresh for the implicit commit of mysql_refresh(), and spider/bugfix.multi_row_trigger_direct_update checks that direct update and delete are switched off. The point is to agree on where this attaches before the bodies are executed.

Hooks of BEFORE COMMIT triggers

Hooks of FOR EACH STATEMENT triggers

Hooks shared by both

The accumulator in the sketch keeps (table, event, row count) runs, activations and per-trigger watermarks, and a statement set keeps row counts and activated events per table, which is enough to exercise savepoints, statement rollback and the per-trigger firing windows.

What the sketch established

BEFORE COMMIT triggers

1. Creating, dropping or replacing a trigger cannot happen while a transaction has rows captured for its table. In the same session CREATE TRIGGER and DROP TRIGGER commit the transaction first; in another session they wait for the transaction's metadata lock on the table. ROLLBACK TO SAVEPOINT releases the metadata lock on a table only when the table was first touched after the savepoint, and then the same rollback removes the table's accumulator entry.
2. A BEFORE COMMIT trigger body cannot be prelocked by the DML of a multi-statement transaction. It runs after the statement's tables are closed and unlocked, possibly many statements later. Its tables have to be opened and locked at the commit boundary, which is in effect a new statement executed at the boundary.
   • LOCK TABLES is the exception: its locks outlive every commit under it, and no table can be locked at a commit in between, so LOCK TABLES locks the tables of BEFORE COMMIT bodies along with those of ROW and FOR EACH STATEMENT bodies. Without that, a body run at COMMIT under LOCK TABLES with autocommit=0 fails with ER_TABLE_NOT_LOCKED, and so does the implicit commit of UNLOCK TABLES, so every such transaction would be rolled back (see BEFORE COMMIT triggers, When a body fails).
   • A statement in autocommit mode prelocks them too. It is the whole transaction, and its triggers fire in its own commit, before its tables are unlocked. Bodies that locked their tables only then would wait for locks while holding the statement's, and table locks such as those of MyISAM detect no deadlock in such a wait.
3. At the commit boundary the subject table is usually closed. It is still open only at the end of an autocommit statement and under LOCK TABLES. Otherwise, at COMMIT, an implicit commit, SAVEPOINT and XA END it is closed. At the end of an autocommit statement the statement's tables are still open and locked, its statement transaction is not finished, and the bodies run on the tables the statement prelocked for them (previous finding).
4. The transactional MDL_SHARED_WRITE on the subject table is still held at every boundary, so its triggers cannot be dropped or replaced between the DML and the commit. The boundary re-opens the table and reaches its triggers through the TABLE, as a ROW trigger does; no copy of the trigger bodies is kept with the accumulator. Re-opening is a table cache hit, but it needs the same apparatus INFORMATION_SCHEMA uses: a backup of the open tables state and a temporary LEX.
5. BEFORE COMMIT triggers fire before trans_commit(), trans_commit_implicit() and trans_begin() start to end the transaction, and in ha_commit_trans() only at the end of an autocommit statement.
   • When a body fails, the statement that caused the commit must not do what it does after a commit: BEGIN, LOCK TABLES and UNLOCK TABLES end LOCK TABLES, SET autocommit=1 switches autocommit on, and a DDL statement runs. The failure rolls the transaction back at the boundary itself, an XA branch included, instead of committing it after the DDL. Leaving it to the end of the statement would not do: mysql_refresh() commits implicitly outside any statement.
   • Either way the bodies fire before ha_check_and_coalesce_trx_read_only(), so engines they write to are counted when choosing one-phase vs two-phase commit, and trans->ha_list has to be re-read afterwards.
6. XA fires at XA END. A prepared XA branch commits through ha_commit_one_phase() and never reaches ha_commit_trans(), and after XA END the branch is IDLE and accepts no DML until XA START ... RESUME makes it ACTIVE again; the next XA END then fires over what it did since.
   • XA END is the last point before XA PREPARE where a body could still write, and XA COMMIT ... ONE PHASE finds nothing left to fire. XA ROLLBACK after XA END rolls back what the bodies wrote.
   • A body that fails at XA END leaves the branch ROLLBACK ONLY, as a deadlock does: XA END, XA PREPARE and ROLLBACK are refused, and XA ROLLBACK ends the branch.
7. Every statement is an implicit savepoint. A statement that fails inside a transaction is rolled back by the engine, so its captured rows have to go too. The statement mark is moved at every statement commit inside a transaction, once that commit has succeeded, and the accumulator is truncated to it on statement rollback, including the rollback of a statement whose commit failed.
8. OR SAVEPOINT fires before the savepoint is established: before an older savepoint of the same name is released and before the MDL savepoint is taken. The body's writes and locks then precede the savepoint and are not undone by ROLLBACK TO it. ROLLBACK TO SAVEPOINT may release MDL taken after the savepoint; that is consistent, because the rows changed after it are truncated from the accumulator.
9. A trigger body or a stored function has a savepoint level of its own. Savepoints set in it are released when it returns, and it cannot roll back to a savepoint set outside it. A ROLLBACK TO SAVEPOINT inside one therefore only reaches back within the same invocation, after the body's own statements have ended.
10. A trigger that fired over rows later rolled back to an earlier savepoint has its watermark clamped, so the next firing covers the replacement rows and nothing twice.
11. All rollbacks pass through ha_rollback_trans(): ROLLBACK, deadlock, disconnect, a failed commit, a BEFORE COMMIT trigger that fails at any commit boundary and XA ROLLBACK.

FOR EACH STATEMENT triggers

1. Statement sets cannot live on the TABLE. A self-join writes one table through two TABLE instances, which would fire the table's triggers twice over half the rows each, and a cached TABLE outlives the statement. The sets are kept on the statement's LEX, each with one entry per table found by the table's name, as in the accumulator.
2. The FOR EACH STATEMENT firing point at finish: is reached by statements inside stored routines and trigger bodies too, with their tables still prelocked. The OK status is already set there, so a failing body has to overwrite it the way a failing commit does.
   • The replication applier does not pass through finish: for row events: a replicated statement ends with the row event flagged STMT_END_F. Rows_log_event::do_apply_event() fires the triggers there after the rows are applied, before the tables of the trigger bodies are taken out of the table list and before the statement transaction is committed.

Capture, shared by both

1. One capture point covers every DML form. The AFTER call in process_triggers() is made once per row actually written, for INSERT, UPDATE, DELETE, REPLACE, INSERT ... ON DUPLICATE KEY UPDATE, LOAD DATA, multi-table statements and rows applied by replication with slave_run_triggers_for_rbr.
   • This holds only because has_triggers(event, TRG_ACTION_AFTER) also counts FOR EACH STATEMENT and BEFORE COMMIT triggers, and every path that bypasses the per-row calls (direct update/delete, delete_all_rows(), REPLACE as an in-place update, batched update/delete) is taken only when it returns false.
   • Direct update also asks match_updatable_columns(), which matches the UPDATE OF lists of ROW triggers; it always matches when the table has a FOR EACH STATEMENT or BEFORE COMMIT UPDATE trigger, or Spider would update the table directly and nothing would be captured.
2. REPLACE fans out. One REPLACE that conflicts on two unique keys is captured as two DELETE rows and one INSERT row. The deleted rows arrive in record[1], not record[0].
3. A matched row is captured even when it does not change. The AFTER UPDATE call is made for every matched row, outside the check whether the row changed, so UPDATE t SET a= a and an INSERT ... ON DUPLICATE KEY UPDATE with the same values are captured as UPDATE with equal OLD and NEW images. A row of INSERT ... ON DUPLICATE KEY UPDATE that conflicts with an existing row is always captured as UPDATE, whether or not the update changes it; a row that conflicts with none is captured as INSERT.
4. Rows changed by foreign key cascades are not seen, the same as for ROW triggers today, and a cascade activates no event of the table it changes. The SQL standard adds those rows to the state changes of the statement for that table (SQL:1999 11.8 GR 7, 8 and 14) and runs its row-level triggers for each of them (10.12 GR 2); the deviation is shared with ROW triggers.
5. Every captured column must be read. ROW triggers mark only the columns the body names. For FOR EACH STATEMENT and BEFORE COMMIT triggers, read_set must cover the union of the columns the accumulator holds, or an UPDATE captures images with holes.

Trigger definitions, shared by both

1. Every loop that means "all triggers of the table" has to cover the new dimension: the destructor, find_trigger(), for_all_triggers(), drop_all_triggers(), INFORMATION_SCHEMA.TRIGGERS and the prepared statement re-validation in TABLE_LIST::is_the_same_definition(). Missing one leaks the trigger body, leaves an orphan .TRN file behind or keeps a stale prelocking set in a prepared statement.
2. The statement re-parse in sp_lex_instr::parse_expr() copies the trigger characteristics by hand and has to copy the granularity as well, or NEW/OLD validation breaks on re-parse.
3. The order of triggers is kept only by the order of their definitions in the .TRG file. FOLLOWS/PRECEDES is cut out of the stored text, and on load every trigger is appended in file order. The file is written by walking the per-event lists, so neither a single order across events nor the position of a multi-event trigger in the lists of its other events survives the next load (MDEV-37711); BEFORE COMMIT triggers are therefore written in the order of their single list.
4. OR SAVEPOINT and the capture list need one token of lookahead, so the code that cuts FOLLOWS/PRECEDES out of the stored definition has to account for it.
5. OF is not a reserved word, and a trigger body may start with a label. After BEFORE COMMIT or FOR EACH STATEMENT, OF always starts the capture list, so a body labeled of has to quote the label. Neither granularity existed before, so no stored trigger changes meaning.

Open questions

1. STATEMENT binary log format. A ROW trigger body is not logged and the replica fires the trigger itself; the statements of a stored procedure are logged and the replica does not re-run the CALL. A BEFORE COMMIT body has to follow exactly one of the two, or the replica applies its writes twice or never.