BEFORE and AFTER

Learn the timing difference.

BEFORE runs before the event, AFTER after. BEFORE for validation, AFTER for logging.

CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000';
  END IF;
END;

This query checks before the update.

The trigger check_balance is created.

BEFORE and AFTER — details

A BEFORE trigger can modify data or stop the operation. AFTER only reads.

For example, BEFORE UPDATE can reject a negative balance; AFTER UPDATE is suited to adding a log entry once the change is written.

Write a BEFORE UPDATE trigger: block negative balance.

Show example solution

CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000';
  END IF;
END

Add BEFORE

CREATE TRIGGER check_balance
____ UPDATE ON accounts

Show answer

CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts