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

Mysql • 07-Feb-2026

by Roxana Haidiner

What MySQL triggers do
MySQL CREATE TRIGGER syntax
Version-specific trigger syntax table
OLD and NEW row values
Example 1: audit product price changes
Example 2: validate data before insert
Example 3: archive deleted rows
Work with triggers in DbSchema
FAQ

A MySQL trigger runs automatically when an INSERT, UPDATE, or DELETE changes a table. Triggers are commonly used for audit trails, validation rules, and keeping helper tables in sync.

What MySQL triggers do

MySQL supports triggers that run:

BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE
AFTER INSERT, AFTER UPDATE, AFTER DELETE

Typical use cases include:

logging important business changes
blocking invalid data before it reaches the table
copying deleted rows into an archive table
updating summary or audit tables automatically

Triggers in MySQL are row-level, so they run once for each affected row.

MySQL CREATE TRIGGER syntax

CREATE [DEFINER = user]
TRIGGER [IF NOT EXISTS] trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
trigger_body;

If the body has multiple statements, use BEGIN ... END with a custom delimiter:

DELIMITER //

CREATE TRIGGER trigger_name
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    -- statements here
END //

DELIMITER ;

Version-specific trigger syntax table

MySQL version	Syntax notes	Practical takeaway
5.7	`CREATE TRIGGER ... BEFORE\|AFTER ... ON table FOR EACH ROW`; supports `FOLLOWS` / `PRECEDES` ordering	no `IF NOT EXISTS`; use explicit trigger names and check for duplicates first
8.0	same core syntax; `IF NOT EXISTS` is available from 8.0.29	better for idempotent deployment scripts
8.4	same modern syntax as late 8.0	current LTS baseline for new projects

Two version details people often miss:

FOLLOWS and PRECEDES let you control order when multiple triggers share the same timing and event.
IF NOT EXISTS is not portable back to MySQL 5.7, so remove it if you maintain older servers.

OLD and NEW row values

Inside a trigger body:

Context	Meaning
`OLD.column_name`	value before the row was updated or deleted
`NEW.column_name`	value being inserted or the updated value after change

Examples:

OLD.price is the previous product price in an UPDATE trigger
NEW.price is the replacement price in an UPDATE trigger
in an INSERT trigger, only NEW makes sense
in a DELETE trigger, only OLD makes sense

Example 1: audit product price changes

This is one of the most common production patterns: log every price change automatically.

CREATE TABLE company.products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10,2)
);

CREATE TABLE company.product_price_log (
    log_id       INT AUTO_INCREMENT PRIMARY KEY,
    product_id   INT NOT NULL,
    old_price    DECIMAL(10,2),
    new_price    DECIMAL(10,2),
    changed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER before_product_price_update
BEFORE UPDATE ON company.products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO company.product_price_log (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
END //

DELIMITER ;

Test it:

INSERT INTO company.products (product_id, product_name, price)
VALUES (1, 'Sample Product', 20.00);

UPDATE company.products
SET price = 25.00
WHERE product_id = 1;

SELECT * FROM company.product_price_log;

This is the classic audit trail trigger pattern and one of the easiest ways to answer "who changed what and when?"

Example 2: validate data before insert

Use a BEFORE INSERT trigger when you want to reject bad data early.

CREATE TABLE orders (
    order_id      INT AUTO_INCREMENT PRIMARY KEY,
    customer_id   INT NOT NULL,
    total_amount  DECIMAL(10,2) NOT NULL
);

DELIMITER //

CREATE TRIGGER before_orders_insert_validate
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.total_amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'total_amount must be zero or positive';
    END IF;
END //

DELIMITER ;

Now any insert with a negative amount fails before the row is written.

Example 3: archive deleted rows

An AFTER DELETE trigger is useful when you need a simple history table.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name   VARCHAR(200) NOT NULL,
    email       VARCHAR(255) NOT NULL
);

CREATE TABLE deleted_customers_archive (
    archive_id   INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    full_name    VARCHAR(200) NOT NULL,
    email        VARCHAR(255) NOT NULL,
    deleted_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER after_customers_delete_archive
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO deleted_customers_archive (customer_id, full_name, email)
    VALUES (OLD.customer_id, OLD.full_name, OLD.email);
END //

DELIMITER ;

That pattern is especially helpful when you want lightweight recovery or reporting without enabling a full CDC pipeline.

Work with triggers in DbSchema

Triggers are SQL objects, but DbSchema still makes the workflow easier because you can design the related tables and validate the impact visually.

connect with the MySQL JDBC driver
inspect the tables involved in the diagram view
run the trigger DDL against the target database after connecting with Connect to Database
document the audit tables and dependencies with interactive schema docs
review nearby schema changes such as keys or lookup tables before deployment

That is particularly useful for trigger-heavy schemas where audit tables, lookup tables, and transactional tables all need to stay understandable.

MySQL trigger tables and audit flow reviewed in DbSchema

If your trigger work depends on foreign keys and relationships, the companion articles Create ER Diagrams for MySQL and MySQL DROP CONSTRAINT syntax are worth reading next.