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
DEFINERnames the account whose privileges the body uses. Without DEFINER, the body uses the privileges of the account that creates the trigger.IF NOT EXISTSgives 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.BEFOREruns the body before MySQL writes the row, andAFTERruns the body once the row is written.INSERT,UPDATEorDELETEis the event that runs the trigger. A trigger has exactly one event.FOR EACH ROWruns the body once for every row that the INSERT, UPDATE or DELETE handles. MySQL has no trigger that runs once per statement.FOLLOWSandPRECEDESset 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_bodyis one statement, or several betweenBEGINandEND. In the body,NEW.columnis a value of the row being written, andOLD.columnis 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:
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';
| sku | name | price |
|---|---|---|
| SHELF-1 | Shelf | 80.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;
| sku | old_price | new_price |
|---|---|---|
| DESK-1 | 250.00 | 275.00 |
| CHAIR-1 | 120.00 | 120.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;
| sku | old_price | new_price |
|---|---|---|
| DESK-1 | 250.00 | 275.00 |
| CHAIR-1 | 120.00 | 120.00 |
| CHAIR-1 | 120.00 | 99.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:
| sku | name | price |
|---|---|---|
| CHAIR-1 | Chair | 99.00 |
| DESK-1 | Desk | 275.00 |
| LAMP-1 | Lamp | NULL |
| SHELF-1 | Shelf | 80.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';
| sku | old_price | new_price |
|---|---|---|
| SHELF-1 | 80.00 | NULL |
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_NAME | ACTION_TIMING | EVENT_MANIPULATION |
|---|---|---|
| products_before_insert | BEFORE | INSERT |
| products_before_update | BEFORE | UPDATE |
| products_after_update | AFTER | UPDATE |
| products_after_delete | AFTER | DELETE |
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.
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.

