How to Use Triggers in MySQL

A trigger is a stored program that MySQL automatically executes when a specific event (INSERT, UPDATE, or DELETE) occurs on a table. Triggers run transparently, meaning the application does not need to call them explicitly. They are commonly used for auditing, enforcing business rules, and maintaining derived data.

BEFORE vs AFTER Triggers

BEFORE triggers fire before the data change is applied, allowing you to validate or modify the incoming data. AFTER triggers fire after the change is committed, making them ideal for logging and cascading updates.

Creating a BEFORE INSERT Trigger

This example normalizes an email to lowercase before the row is inserted. The NEW keyword refers to the incoming row.

DELIMITER //

CREATE TRIGGER normalize_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
END //

DELIMITER ;

Creating an AFTER INSERT Trigger (Audit Log)

After a new order is created, this trigger records the event in an audit table. The NEW keyword gives access to the newly inserted row's values.

DELIMITER //

CREATE TRIGGER log_new_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action, record_id, created_at)
  VALUES ('orders', 'INSERT', NEW.id, NOW());
END //

DELIMITER ;

BEFORE UPDATE Trigger

The OLD keyword references the row's values before the update, while NEW references the values after. This trigger automatically sets an updated_at timestamp on every update.

DELIMITER //

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //

DELIMITER ;

AFTER DELETE Trigger

This trigger archives a deleted employee record before the row disappears. The OLD keyword gives access to the deleted row's values.

DELIMITER //

CREATE TRIGGER archive_deleted_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employees_archive (id, first_name, last_name, email, deleted_at)
  VALUES (OLD.id, OLD.first_name, OLD.last_name, OLD.email, NOW());
END //

DELIMITER ;

Viewing and Dropping Triggers

List triggers with SHOW TRIGGERS and remove them with DROP TRIGGER.

SHOW TRIGGERS FROM my_database;

DROP TRIGGER IF EXISTS normalize_email;

Best Practices

Keep trigger logic short and focused. Complex triggers slow down every INSERT, UPDATE, or DELETE on the table. Avoid triggers that modify other tables with their own triggers to prevent hard-to-debug cascading chains. Document your triggers thoroughly because they execute invisibly and can surprise developers who are unaware of them.