The idea: code that runs inside the server, row by row

A trigger is a piece of SQL tied to a table. MariaDB runs it by itself when an INSERT, UPDATE or DELETE touches a row of that table. A stored procedure is a named program kept in the database. It runs only when a client calls it with CALL. Both run inside the server, next to the data.

Time runs down. One statement, INSERT INTO orders VALUES (1, 40), (2, 25). Row 1: BEFORE INSERT orders_bi_check, write row id 3, AFTER INSERT orders_ai_total sets Ana's total 50 to 90. Row 2: BEFORE INSERT, write row id 4, AFTER INSERT sets Ben's total 30 to 55. Then autocommit COMMIT. A bracket marks all of it as one statement.
A trigger fires once per row: BEFORE, write, AFTER, then the next row. All of it is one statement.

MariaDB has only FOR EACH ROW triggers. A statement that touches two rows fires each trigger twice. It runs the BEFORE triggers, writes the row and runs the AFTER triggers, and then does the same for the next row (Demo: BEFORE and AFTER fire for each row). The canvas uses two tables. orders(id, customer_id, amount) holds the orders, and customers(id, name, total) holds a total for each customer.

CREATE TRIGGER, NEW and OLD

CREATE TRIGGER orders_ai_total AFTER INSERT ON orders
FOR EACH ROW
  UPDATE customers SET total = total + NEW.amount
  WHERE id = NEW.customer_id;

Inside the trigger, NEW is the row as it will be after the statement, and OLD is the row as it was before it.

eventOLDNEW
INSERTnonethe new row; a BEFORE trigger may change it with SET NEW.col = ...
UPDATEthe row beforethe row after
DELETEthe row that goesnone

In a BEFORE INSERT trigger, NEW.id is still 0. The AUTO_INCREMENT value is taken only when the row is written. Demo: UPDATE and DELETE use OLD and NEW changes order 1 from 50 to 80, so the trigger sees OLD.amount 50 and NEW.amount 80. Then it deletes order 2, and the trigger sees only OLD.amount 30.

A derived total kept by triggers

customers.total is always the sum of that customer's orders. Three AFTER triggers keep it right. AFTER INSERT adds NEW.amount. AFTER UPDATE subtracts OLD.amount and adds NEW.amount. AFTER DELETE subtracts OLD.amount. Nobody can forget to update the total, because the server does it for every statement from every client. The trigger's UPDATE is part of the same statement, so it lands in the same undo log (bottom of the tables panel) as the row itself.

Rejecting a row with SIGNAL

CREATE TRIGGER orders_bi_check BEFORE INSERT ON orders
FOR EACH ROW
  IF NEW.amount <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amount must be positive';
  END IF;

SIGNAL raises an error. SQLSTATE '45000' means "user-defined error", and the client sees ERROR 1644 (45000): amount must be positive. 1644 is the default error number for that SQLSTATE. A BEFORE trigger that fails stops the row from being written.

With InnoDB, the trigger runs inside the transaction of the statement that fired it. So the whole statement fails, not just one row. In Demo: SIGNAL rolls back the whole statement, INSERT ... VALUES (1, 40), (2, -5) first writes row 1, and the AFTER trigger raises Ana's total from 50 to 90. Then row 2 fails the check. InnoDB applies the statement's undo lines, newest first, so the row goes away and Ana's total is 50 again. With a non-transactional engine such as MyISAM, row 1 would stay. The two ids the INSERT reserved are not handed back, so the next order gets id 5.

Left: one INSERT with rows (1, 40) and (2, -5). Row 1 is written, total 50 to 90. Row 2 hits SIGNAL. Both changes are undone: orders 2 rows, Ana 50. Right: CALL add_orders(2, 20, 3) runs three INSERT statements. INSERT 20 and INSERT 10 each commit with autocommit. INSERT 0 hits SIGNAL and only that statement fails. Ben's total ends at 60 and 2 new rows stay.
One statement is all or nothing. A CALL is not: each statement inside it commits on its own.

Stored procedures: parameters, a loop, CALL

DELIMITER //
CREATE PROCEDURE add_orders(IN p_cust INT, IN p_first INT,
                            IN p_n INT, OUT p_done INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < p_n DO
    INSERT INTO orders (customer_id, amount)
      VALUES (p_cust, p_first - 10 * i);
    SET i = i + 1;
  END WHILE;
  SET p_done = i;
END //
DELIMITER ;

CALL add_orders(1, 30, 3, @done);
SELECT @done;   -- 3

DELIMITER is a command of the mariadb client. It lets the semicolons inside the body pass through. IN parameters carry values in, and an OUT parameter carries a value back into a session variable such as @done. DECLARE makes a local variable that lives only during the CALL. WHILE ... DO ... END WHILE is one of the loops (also LOOP, REPEAT ... UNTIL and FOR). Demo: a stored procedure with a loop inserts 30, 20 and 10 for Ana. The triggers fire for each row, so her total goes from 50 to 110 and @done becomes 3.

Errors in a CALL, and transactions

A CALL is not one statement. With autocommit = 1, each INSERT inside the procedure commits on its own. CALL add_orders(2, 20, 3, @done) inserts 20 and 10, and then the third pass tries amount 0. The check trigger raises 1644, and only that INSERT fails. The procedure has no handler, so the error also ends the CALL and SET p_done never runs. The first two rows stay, and Ben's total is 60 (Demo: an error inside a CALL keeps earlier rows).

To make the CALL all or nothing, run it inside a transaction. After START TRANSACTION, the changes wait in the undo log. A failed statement undoes only itself, and the transaction stays open. Then ROLLBACK undoes the rest, and Ben is back to 30 (Demo: the CALL in a transaction, then ROLLBACK). A procedure may run START TRANSACTION, COMMIT and ROLLBACK itself, often in DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END. A trigger may not, because it is part of someone else's statement. How the undo log is kept and purged is on InnoDB undo and purge, and how the redo log makes COMMIT survive a crash is on write-ahead logging and recovery.

Several triggers: FOLLOWS and PRECEDES

A table may have several triggers with the same timing and event. They run in ACTION_ORDER (see INFORMATION_SCHEMA.TRIGGERS). A new trigger goes last unless it says FOLLOWS other_trigger or PRECEDES other_trigger.

Left, CREATE TRIGGER orders_bi_abs with no order clause: ACTION_ORDER 1 orders_bi_check, 2 orders_bi_abs. INSERT (2, -5): the check sees -5 and fails with ERROR 1644. Right, CREATE OR REPLACE TRIGGER orders_bi_abs PRECEDES orders_bi_check: 1 orders_bi_abs makes NEW.amount 5, 2 orders_bi_check passes, the row is written and Ben's total goes from 30 to 35.
Same two triggers, other order, other result: put the trigger that fixes NEW before the one that checks it.

In Demo: trigger order, added last vs PRECEDES, orders_bi_abs (SET NEW.amount = ABS(NEW.amount)) is first created without an order clause, so it runs after the check, and -5 is rejected. CREATE OR REPLACE TRIGGER ... PRECEDES orders_bi_check moves it first. Then -5 becomes 5 before the check sees it. CREATE TRIGGER is DDL, so it commits any open transaction first.

In practice

  • Keep triggers short. They run for every row of every statement, and they are easy to forget when you read the application code.
  • Use BEFORE triggers to check or fix NEW, and AFTER triggers to change other tables. A trigger cannot change the table it fires on.
  • Use InnoDB, so that a failed trigger takes its whole statement back.
  • Wrap a procedure that makes several changes in a transaction, or give it an exit handler that rolls back.

Who may create triggers and procedures (the TRIGGER, CREATE ROUTINE and EXECUTE privileges) is a GRANT question: see GRANT and REVOKE. The statements the triggers run work on rows the same way any query does; for joins see SQL joins on rows. What other sessions see of the uncommitted rows is on MVCC and isolation levels.

See also MariaDB Integrity Constraints and Foreign Key Actions for the rules that need no trigger: NOT NULL, UNIQUE, CHECK and foreign keys, each with its error number.

What the page leaves out

Only one session, one InnoDB database and one procedure are drawn. Stored functions, cursors, condition handlers (DECLARE ... HANDLER, RESIGNAL, GET DIAGNOSTICS), DEFINER and SQL SECURITY are left out. So are triggers fired by INSERT ... ON DUPLICATE KEY UPDATE and REPLACE. The SQL standard's FOR EACH STATEMENT triggers and transition tables are not available in MariaDB. Foreign key actions such as ON DELETE CASCADE do not fire triggers, and TRUNCATE TABLE fires none. The check covers INSERT only: an UPDATE to a negative amount would need a BEFORE UPDATE trigger too (from MariaDB 12.0, one trigger can name INSERT OR UPDATE).

On the mind map

Open the Databases & Storage mind mapDatabases& StorageSchema and storage: how rows land on diskSchema and storageIndexes: find rows without a full scanIndexesRunning a query: parse, plan, executeRunning a queryCost-based optimizer: estimate rows, pick cheapest planCost-based optimizerQuery rewrites: same answer, simpler queryQuery rewritesTransactions: ACID: isolation and recoveryTransactionsApps and connections: the client side of the databaseApps and connectionsMongoDB: documents, replica sets, shardsMongoDBHow MySQL runs a queryHow MySQL runs a querybuffer pool, undo, redo, binlogHow PostgreSQL runs a queryHow PostgreSQL runs a querynew tuple per update, VACUUM, WALSELECT clause orderSELECT clause orderFROM, WHERE, GROUP BY, HAVING, SELECTJoin algorithmsJoin algorithmsnested loop, hash, merge: pick by sizeExternal merge sortExternal merge sort2N I/Os per pass, merge B−1 runs at onceFile organizationsFile organizationsHeap inserts, sorted ranges, hash findsMySQL JOIN, GROUP, ORDERMySQL JOIN, GROUP, ORDERtemporary table and filesortPostgres JOIN, GROUP, ORDERPostgres JOIN, GROUP, ORDERHashAggregate or Sort, by work_memRelational algebraRelational algebraselect, project, join: tables in, table outSQL joins on rowsSQL joins on rowsOuter joins pad unmatched rows with NULLSQL subqueriesSQL subqueriesOne NULL makes NOT IN return no rowsTriggers and proceduresPer row; a CALL is not one statementWindow functionsWindow functionsaggregate per row without collapsingOpen the Databases & Storage mind mapDatabases & Storageparse, plan, executeRunning a queryHow MySQL runs a queryHow MySQL runs a querybuffer pool, undo, redo, binlogHow PostgreSQL runs a queryHow PostgreSQL runs a querynew tuple per update, VACUUM, WALSELECT clause orderSELECT clause orderFROM, WHERE, GROUP BY, HAVING, SELECTJoin algorithmsJoin algorithmsnested loop, hash, merge: pick by sizeExternal merge sortExternal merge sort2N I/Os per pass, merge B−1 runs at onceFile organizationsFile organizationsHeap inserts, sorted ranges, hash findsMySQL JOIN, GROUP, ORDERMySQL JOIN, GROUP, ORDERtemporary table and filesortPostgres JOIN, GROUP, ORDERPostgres JOIN, GROUP, ORDERHashAggregate or Sort, by work_memRelational algebraRelational algebraselect, project, join: tables in, table outSQL joins on rowsSQL joins on rowsOuter joins pad unmatched rows with NULLSQL subqueriesSQL subqueriesOne NULL makes NOT IN return no rowsTriggers and proceduresPer row; a CALL is not one statementWindow functionsWindow functionsaggregate per row without collapsing
This page on the Databases & Storage mind map · 55 pages in 8 branches · open it to see the whole map and test yourself in recall mode.