Back to all posts

MySQL CREATE TRIGGER Syntax, BEFORE/AFTER Examples, and Audit Patterns

For developers who write SQL for MySQL and want the database itself to check or record every change to a table, whatever client made it.

On this page

A trigger makes MySQL run a set of statements automatically whenever a row of one table is inserted, updated or deleted. For example, a trigger on a products table can write every price change into a log table. The trigger lives in the database, so a change from the application, a script or the console is checked and recorded the same way.

You create every trigger with one statement:

CREATE
  [DEFINER = user]
  TRIGGER [IF NOT EXISTS] trigger_name
  { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
  ON table_name FOR EACH ROW
  [{ FOLLOWS | PRECEDES } other_trigger]
  trigger_body
  • DEFINER names the account whose privileges the body uses. Without DEFINER, the body uses the privileges of the account that creates the trigger.
  • IF NOT EXISTS gives a note, a message milder than a warning, instead of an error when the table already has a trigger of that name. MySQL accepts IF NOT EXISTS from 8.0.29.
  • BEFORE runs the body before MySQL writes the row, and AFTER runs the body once the row is written.
  • INSERT, UPDATE or DELETE is the event that runs the trigger. A trigger has exactly one event.
  • FOR EACH ROW runs the body once for every row that the INSERT, UPDATE or DELETE handles. MySQL has no trigger that runs once per statement.
  • FOLLOWS and PRECEDES set the order of several triggers with the same timing and event, such as two BEFORE UPDATE triggers. Without FOLLOWS or PRECEDES, these triggers run in the order of their creation.
  • trigger_body is one statement, or several between BEGIN and END. In the body, NEW.column is a value of the row being written, and OLD.column is the value before the change. An INSERT has only NEW, and a DELETE has only OLD.

Create two sample tables, products and price_log, and fill products:

CREATE TABLE products (
  sku   VARCHAR(20) PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  price DECIMAL(8,2)
);

INSERT INTO products VALUES
  ('DESK-1',  'Desk',  250.00),
  ('CHAIR-1', 'Chair', 120.00),
  ('LAMP-1',  'Lamp',  NULL);

CREATE TABLE price_log (
  sku       VARCHAR(20),
  old_price DECIMAL(8,2),
  new_price DECIMAL(8,2)
);

The lamp has no price yet. The price_log table starts empty, and the triggers below fill it.

Run the statements in order. Each one changes the rows that the next one reads. MySQL 9.1 and 8.0.29 returned the same result for every statement.

A trigger runs either before or after MySQL writes the row. For an UPDATE of the desk's price, MySQL runs these steps:

For UPDATE products SET price = 275.00 WHERE sku = 'DESK-1', MySQL runs the BEFORE UPDATE trigger, which can change NEW.price or stop the row with an error, then writes the row, then runs the AFTER UPDATE trigger, which reads OLD.price 250.00 and NEW.price 275.00 and can write to price_log

BEFORE INSERT

A BEFORE INSERT trigger can change a new row's values with SET NEW.column before MySQL writes the row. For example, the trigger can store every sku in upper case, whatever case the application sent.

CREATE TRIGGER products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
SET NEW.sku = UPPER(NEW.sku);

INSERT INTO products VALUES ('shelf-1', 'Shelf', 80.00);

SELECT * FROM products WHERE name = 'Shelf';
skunameprice
SHELF-1Shelf80.00

The INSERT sent shelf-1, and MySQL stored SHELF-1. The trigger's body is a single statement and needs no BEGIN and END.

OLD and NEW

In an UPDATE trigger, OLD holds the row's values before the change, and NEW holds the values after it. For example, an AFTER UPDATE trigger can copy the old and the new price of a product into price_log.

CREATE TRIGGER products_after_update
AFTER UPDATE ON products
FOR EACH ROW
INSERT INTO price_log VALUES (OLD.sku, OLD.price, NEW.price);

UPDATE products SET price = 275.00 WHERE sku = 'DESK-1';
UPDATE products SET name = 'Office chair' WHERE sku = 'CHAIR-1';

SELECT * FROM price_log;
skuold_pricenew_price
DESK-1250.00275.00
CHAIR-1120.00120.00

The DESK-1 row holds the desk's price before and after the UPDATE. The CHAIR-1 row records no price change, because only the chair's name changed. The trigger runs for every row that the UPDATE matches.

If the UPDATE fails, MySQL writes no row and runs no AFTER trigger, so price_log gets nothing.

BEGIN ... END and DELIMITER

BEGIN and END hold a trigger body of more than one statement. For example, the AFTER UPDATE trigger can log only the rows whose price changed.

In the mysql command-line client, change the delimiter, the text that ends a statement, before CREATE TRIGGER. Otherwise the mysql client ends CREATE TRIGGER at the first semicolon inside the body.

Drop products_after_update before you create its new version. MySQL has no CREATE OR REPLACE TRIGGER.

DROP TRIGGER products_after_update;

DELIMITER //

CREATE TRIGGER products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF OLD.price <> NEW.price THEN
    INSERT INTO price_log VALUES (OLD.sku, OLD.price, NEW.price);
  END IF;
END//

DELIMITER ;

UPDATE products SET name = 'Chair' WHERE sku = 'CHAIR-1';
UPDATE products SET price = 99.00 WHERE sku = 'CHAIR-1';

SELECT * FROM price_log;
skuold_pricenew_price
DESK-1250.00275.00
CHAIR-1120.00120.00
CHAIR-1120.0099.00

The UPDATE that renamed the chair added no row. The UPDATE that changed the chair's price added the third row.

DELIMITER // makes // end a statement until DELIMITER ; restores the semicolon. DELIMITER is a command of the mysql client, and the server never receives it.

The <> comparison misses a change from a NULL price. The fix is under What goes wrong.

SIGNAL

SIGNAL stops the statement with an error of your own. For example, a BEFORE UPDATE trigger can reject a price that rises by more than 50%.

DELIMITER //

CREATE TRIGGER products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price > OLD.price * 1.5 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'A price can rise by at most 50% at a time';
  END IF;
END//

DELIMITER ;

UPDATE products SET price = 500.00 WHERE sku = 'DESK-1';

MySQL rejects the UPDATE with the trigger's message:

ERROR 1644 (45000) at line 1: A price can rise by at most 50% at a time

SELECT * FROM products now returns:

skunameprice
CHAIR-1Chair99.00
DESK-1Desk275.00
LAMP-1LampNULL
SHELF-1Shelf80.00

In a BEFORE trigger, SIGNAL rejects the row before MySQL writes it. MySQL then never runs the AFTER trigger, so price_log gets no new row. A CHECK constraint can't express the 50% rule, because the rule compares the new price with the old one.

SQLSTATE is a five-character error code. The manual names '45000' as the SQLSTATE of an error that you define yourself.

An error in a trigger fails the whole statement. On an InnoDB table, MySQL rolls back the rows that the statement had already changed.

AFTER DELETE

An AFTER DELETE trigger runs after MySQL deletes a row, and OLD holds the deleted row's values. For example, the trigger can copy the last price of a deleted product into price_log.

CREATE TRIGGER products_after_delete
AFTER DELETE ON products
FOR EACH ROW
INSERT INTO price_log VALUES (OLD.sku, OLD.price, NULL);

DELETE FROM products WHERE sku = 'SHELF-1';

SELECT * FROM price_log WHERE sku = 'SHELF-1';
skuold_pricenew_price
SHELF-180.00NULL

The SHELF-1 row holds the shelf's price before the DELETE. Its new_price is NULL, because a DELETE has no NEW row.

SHOW TRIGGERS and INFORMATION_SCHEMA.TRIGGERS

SHOW TRIGGERS lists the triggers of the current database, each with its whole body. For a short list of names, query the TRIGGERS table of INFORMATION_SCHEMA instead.

SELECT trigger_name, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = DATABASE()
  AND event_object_table = 'products';
TRIGGER_NAMEACTION_TIMINGEVENT_MANIPULATION
products_before_insertBEFOREINSERT
products_before_updateBEFOREUPDATE
products_after_updateAFTERUPDATE
products_after_deleteAFTERDELETE

The query returns one row for each trigger on products. SHOW CREATE TRIGGER products_after_update returns that trigger's full definition. DROP TRIGGER IF EXISTS removes a trigger, and gives a note instead of an error when the trigger doesn't exist.

What goes wrong

A missing DELIMITER

Take the BEGIN ... END trigger above, end it with END;, and run it without DELIMITER //. The mysql client then sends the trigger in pieces, and MySQL rejects the first piece, up to the first semicolon in the body:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

Two more 1064 errors follow. END IF; and END; reach the server as separate statements.

Set the delimiter before CREATE TRIGGER, end the trigger with that delimiter, and restore the semicolon afterwards.

A NULL that <> misses

Change the lamp's price from NULL to 40.00. products_after_update logs nothing:

UPDATE products SET price = 40.00 WHERE sku = 'LAMP-1';

SELECT * FROM price_log WHERE sku = 'LAMP-1';

The SELECT returns no rows. NULL <> 40.00 is NULL, not true, so the IF never runs its INSERT. Write the condition with <=>, MySQL's NULL-safe equal, which returns true or false even for a NULL: IF NOT (OLD.price <=> NEW.price) THEN.

Changing the row in an AFTER trigger

An AFTER trigger can't change the row it runs for. MySQL refuses to create an AFTER trigger that sets NEW:

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);
ERROR 1362 (HY000) at line 1: Updating of NEW row is not allowed in after trigger

MySQL creates an AFTER trigger that updates its own table, but the trigger fails at the first INSERT:

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
UPDATE products SET name = UPPER(name) WHERE sku = NEW.sku;

INSERT INTO products VALUES ('BIN-1', 'Bin', 15.00);
ERROR 1442 (HY000) at line 1: Can't update table 'products' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The INSERT fails, and MySQL stores no bin. Drop the trigger with DROP TRIGGER products_after_insert. To change the new row, use SET NEW in a BEFORE trigger, like products_before_insert.

Error 1419 without SUPER

CREATE TRIGGER needs the TRIGGER privilege on the table. When binary logging is on, as it is by default, MySQL also asks for the SUPER privilege or for the server variable log_bin_trust_function_creators set to 1. A user with only the TRIGGER privilege gets:

ERROR 1419 (HY000) at line 1: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

The manual describes the SUPER requirement for servers with binary logging, and the variable that lifts it.

TRUNCATE and cascaded deletes

TRUNCATE TABLE products empties the table without running products_after_delete, because TRUNCATE doesn't use DELETE. When a foreign key with ON DELETE CASCADE deletes rows of another table, MySQL runs no trigger for those rows either. The manual states both facts, and runs on MySQL 9.1 and 8.0.29 confirmed them.

The MySQL 9.7 manual describes a setting, enable_cascade_triggers, that turns on triggers for cascades in some InnoDB configurations.

Triggers in DbSchema

The price_log table has no foreign key to products. Only the trigger links the two tables: a change to products writes a row to price_log. DbSchema reverse-engineers a MySQL database: it reads the tables and their triggers into a model.

DbSchema's Schema Documentation can list each table's triggers, with their SQL, under the table. Choose File → Export As... → HTML5/PDF Documentation, and tick Triggers and Procs/Triggers Text under Content.

The DbSchema Schema Documentation dialog, with the Triggers and Procs/Triggers Text boxes under Content

Download DbSchema at https://dbschema.com/download.html, connect it to MySQL, and generate the documentation of the tables that your triggers write to. Connecting and reverse-engineering are in the free Community Edition. Community documents a diagram of up to 12 tables, and DbSchema Pro documents any diagram.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download