Triggers in MySQL

What Is a MySQL Trigger?

A trigger is a named database object attached to a permanent table. MySQL activates it automatically for every row affected by its configured INSERT, UPDATE or DELETE event. You do not call a trigger with CALL.

Good uses include enforcing a rule that must apply to every writer, deriving a bounded column value and creating a small audit row. Triggers are a poor hiding place for long workflows, network calls or logic that application developers cannot discover.

CREATE TRIGGER Syntax and NEW/OLD Matrix

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name FOR EACH ROW
  trigger_body;

In a BEFORE trigger, SET NEW.column = expression can change a permitted new value. OLD is read-only. In an AFTER trigger, row values already represent the completed row operation and cannot be changed through NEW or OLD.

Triggers are row-level: an UPDATE affecting 500 rows activates its UPDATE trigger 500 times. TRUNCATE TABLE and DROP TABLE do not activate DELETE triggers.

Build a Reproducible Orders Audit Lab

DROP TABLE IF EXISTS order_audit_lab;
DROP TABLE IF EXISTS orders_trigger_lab;

CREATE TABLE orders_trigger_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  total_amount DECIMAL(10,2) NOT NULL,
  updated_at TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT chk_order_status
    CHECK (status IN ('PENDING','PAID','CANCELLED'))
) ENGINE = InnoDB;

CREATE TABLE order_audit_lab (
  audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  action_name VARCHAR(20) NOT NULL,
  old_status VARCHAR(20),
  new_status VARCHAR(20),
  old_amount DECIMAL(10,2),
  new_amount DECIMAL(10,2),
  changed_by VARCHAR(288) NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_audit_order_time (order_id, changed_at)
) ENGINE = InnoDB;

The CHECK constraint owns the finite status domain. A trigger will add the cross-column amount rule and an audit entry. Prefer declarative constraints for rules they can express; they are clearer to optimizers, tools and readers.

BEFORE Trigger for Validation and Normalization

DELIMITER //

CREATE TRIGGER bi_orders_validate
BEFORE INSERT ON orders_trigger_lab
FOR EACH ROW
BEGIN
  SET NEW.status = UPPER(TRIM(NEW.status));

  IF NEW.total_amount < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'total_amount cannot be negative';
  END IF;
END //

DELIMITER ;

INSERT INTO orders_trigger_lab
  (order_id, customer_id, status, total_amount)
VALUES (1, 101, ' paid ', 1200.00);

SELECT order_id, status, total_amount
FROM orders_trigger_lab;

INSERT INTO orders_trigger_lab
VALUES (2, 102, 'PENDING', -10.00, DEFAULT);

Basic column type checks happen before trigger activation, so a BEFORE trigger cannot rescue input that is already invalid for the column type. Validate again in the application for fast feedback, but keep the database rule when all writers must obey it.

AFTER UPDATE Trigger for an Audit Trail

DELIMITER //

CREATE TRIGGER au_orders_audit
AFTER UPDATE ON orders_trigger_lab
FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status)
     OR NOT (OLD.total_amount <=> NEW.total_amount) THEN
    INSERT INTO order_audit_lab
      (order_id, action_name,
       old_status, new_status,
       old_amount, new_amount, changed_by)
    VALUES
      (NEW.order_id, 'UPDATE',
       OLD.status, NEW.status,
       OLD.total_amount, NEW.total_amount,
       SESSION_USER());
  END IF;
END //

DELIMITER ;

UPDATE orders_trigger_lab
SET status = 'CANCELLED', total_amount = 1100.00
WHERE order_id = 1;

SELECT order_id, action_name, old_status, new_status,
       old_amount, new_amount
FROM order_audit_lab;

The NULL-safe equality operator <=> avoids missing a transition involving NULL. SESSION_USER() records the connected account; CURRENT_USER() in a trigger normally reflects the trigger definer, which answers a different audit question.

Statement Failure, Rollback and Multiple-Trigger Order

AFTER means after each row operation, not after COMMIT. For transactional InnoDB tables, the row change and trigger's transactional side effects belong to the same statement and transaction:

START TRANSACTION;
UPDATE orders_trigger_lab
SET status = 'PAID'
WHERE order_id = 1;

SELECT COUNT(*) FROM order_audit_lab; -- new row visible here
ROLLBACK;

SELECT status FROM orders_trigger_lab WHERE order_id = 1;
SELECT COUNT(*) FROM order_audit_lab; -- rolled-back audit is gone

If a BEFORE or AFTER trigger raises an error, the statement fails. Transaction outcome beyond that depends on the error and transaction handling, so applications must handle errors and issue the intended rollback.

MySQL permits multiple triggers with the same table, timing and event. By default they activate in creation order; make dependencies explicit:

CREATE TRIGGER au_orders_metrics
AFTER UPDATE ON orders_trigger_lab
FOR EACH ROW FOLLOWS au_orders_audit
  INSERT INTO metrics_queue(order_id, event_name)
  VALUES (NEW.order_id, 'ORDER_UPDATED');

Prefer independent triggers. A fragile chain of side effects is harder to deploy and reason about.

Inspect, Replace and Drop Triggers Safely

SHOW TRIGGERS LIKE 'orders_trigger_lab';
SHOW CREATE TRIGGER au_orders_audit;

SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION,
       EVENT_OBJECT_TABLE, ACTION_ORDER, DEFINER
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
  AND EVENT_OBJECT_TABLE = 'orders_trigger_lab'
ORDER BY EVENT_MANIPULATION, ACTION_TIMING, ACTION_ORDER;

DROP TRIGGER IF EXISTS au_orders_audit;

• CREATE TRIGGER requires the TRIGGER privilege for the table.
• The trigger executes with its definer security context; choose a durable least-privileged account.
• Keep definitions in version-controlled migrations and test upgrade/rollback scripts.
• Adding a column can break positional INSERT ... VALUES; always name audit columns.
• Benchmark multi-row DML because trigger work multiplies per affected row.

Trigger Design and Test Checklist

1. Use CHECK, foreign keys and generated columns before a trigger where possible.
2. Name timing, event and table clearly, such as bu_orders_validate.
3. Test INSERT, UPDATE and DELETE separately; verify the NEW/OLD matrix.
4. Test one row, many rows, no matching rows and NULL transitions.
5. Test trigger-raised errors and explicit transaction rollback.
6. Keep the body deterministic, bounded and free of hidden external work.
7. Index lookup and audit-table columns used by the trigger.
8. Review locks, deadlocks, replication and restore behavior.
9. Document definer, permissions, order and ownership.
10. Monitor latency and audit growth after deployment.

Continue with BEFORE vs AFTER triggers, compare stored procedures and review transactions.

Official References

• MySQL 8.4: CREATE TRIGGER Statement
• MySQL 8.4: Trigger Syntax and Examples
• MySQL 8.4: Stored Program Restrictions

Timing, events, NEW/OLD, trigger ordering, privileges and restrictions were checked against the official MySQL 8.4 manual.