BEFORE and AFTER Triggers

BEFORE vs AFTER: The Exact Difference

A MySQL trigger combines an action time—BEFORE or AFTER—with an event—INSERT, UPDATE or DELETE. The time is relative to each affected row:

• BEFORE: validate, reject or assign a permitted NEW value before the row is changed.
• AFTER: react after the row operation has succeeded, commonly by inserting an audit or dependent row.

Timing, Events and Value Access

OLD is always read-only. In a BEFORE trigger, changing NEW requires the necessary privileges. Generated columns cannot be referenced through NEW.column or OLD.column. For an AUTO_INCREMENT column, the NEW value is 0 in a BEFORE trigger if the statement did not explicitly set it.

Basic column value checks occur before trigger activation. A VARCHAR-to-DATE conversion that is invalid for the column can fail before a BEFORE trigger gets a chance to repair it.

Create a Reproducible Timing Lab

DROP TABLE IF EXISTS order_timing_audit;
DROP TABLE IF EXISTS orders_timing_lab;

CREATE TABLE orders_timing_lab (
  order_id INT PRIMARY KEY,
  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,
  CHECK (status IN ('PENDING','PAID','CANCELLED'))
) ENGINE = InnoDB;

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

The CHECK constraint expresses the finite status set. Triggers add trimming/case normalization, a cross-column cancellation rule and a change audit. Splitting responsibilities makes the example easier to inspect and test.

BEFORE INSERT and BEFORE UPDATE Examples

DELIMITER //

CREATE TRIGGER bi_orders_timing
BEFORE INSERT ON orders_timing_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 //

CREATE TRIGGER bu_orders_timing
BEFORE UPDATE ON orders_timing_lab
FOR EACH ROW
BEGIN
  SET NEW.status = UPPER(TRIM(NEW.status));

  IF NEW.status = 'CANCELLED'
     AND NEW.total_amount > OLD.total_amount THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'cancelled order amount cannot increase';
  END IF;
END //

DELIMITER ;

INSERT INTO orders_timing_lab
  (order_id, status, total_amount)
VALUES (1, ' paid ', 1200.00);

SELECT order_id, status, total_amount
FROM orders_timing_lab;

BEFORE is appropriate because the normalized status must be the value stored in the same row. SIGNAL SQLSTATE '45000' provides a deliberate business-rule error instead of silently corrupting meaning.

AFTER UPDATE Example: Record the Final Change

DELIMITER //

CREATE TRIGGER au_orders_timing
AFTER UPDATE ON orders_timing_lab
FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status)
     OR NOT (OLD.total_amount <=> NEW.total_amount) THEN
    INSERT INTO order_timing_audit
      (order_id, old_status, new_status,
       old_amount, new_amount)
    VALUES
      (NEW.order_id, OLD.status, NEW.status,
       OLD.total_amount, NEW.total_amount);
  END IF;
END //

DELIMITER ;

UPDATE orders_timing_lab
SET status = ' cancelled ', total_amount = 1100.00
WHERE order_id = 1;

SELECT order_id, old_status, new_status,
       old_amount, new_amount
FROM order_timing_audit;

The BEFORE UPDATE trigger normalizes NEW.status first. The AFTER UPDATE trigger sees that final normalized value and records it. NULL-safe equality <=> makes change detection reliable if nullable columns are later audited.

Prove That AFTER Is Not “After Commit”

SELECT COUNT(*) AS audit_before
FROM order_timing_audit;

START TRANSACTION;
UPDATE orders_timing_lab
SET total_amount = 1000.00
WHERE order_id = 1;

SELECT total_amount FROM orders_timing_lab WHERE order_id = 1;
SELECT COUNT(*) AS audit_inside_tx FROM order_timing_audit;

ROLLBACK;

SELECT total_amount FROM orders_timing_lab WHERE order_id = 1;
SELECT COUNT(*) AS audit_after_rollback FROM order_timing_audit;

Both tables use InnoDB, so their changes are transactional. A nontransactional table would not provide the same rollback guarantee. A trigger cannot create a true “after commit” callback; publish-after-commit workflows normally use an application transaction plus a transactional outbox processed after commit.

Multiple Triggers and Activation Order

MySQL allows multiple triggers with the same table, action time and event. Without an order clause, they activate in creation order. Use PRECEDES or FOLLOWS with an existing trigger when order is genuinely required:

CREATE TRIGGER bu_orders_timing_round
BEFORE UPDATE ON orders_timing_lab
FOR EACH ROW FOLLOWS bu_orders_timing
  SET NEW.total_amount = ROUND(NEW.total_amount, 2);

PRECEDES/FOLLOWS applies to another trigger with the same event and action time. Inspect ACTION_ORDER in information_schema.TRIGGERS.

When to Choose Each Timing and How to Test

1. Test valid, boundary, invalid and NULL inputs.
2. Test multi-row statements; triggers run once per row.
3. Verify OLD/NEW for every configured event.
4. Test normalization before audit to confirm final values.
5. Test error, rollback, retry and deadlock handling.
6. Inspect trigger order and definer after deployment.
7. Benchmark bulk DML and audit-table indexes.

Review the complete MySQL triggers tutorial, transactions and locks and deadlocks.

Official References

• MySQL 8.4: CREATE TRIGGER Statement
• MySQL 8.4: Trigger Syntax and Examples
• MySQL 8.4: START TRANSACTION, COMMIT and ROLLBACK

Timing, write access to NEW/OLD, ordering and transaction behavior were checked against official MySQL 8.4 documentation.