Integrity with triggers

Add extra integrity rules with triggers.

A trigger is code that runs automatically on an event. Used for custom integrity checks.

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

This query prevents inserting a negative balance.

The trigger check_balance is created.

Integrity with triggers — details

A trigger checks more complex conditions than CHECK. It can relate multiple tables.

For example, rejecting an order when it exceeds the customer's credit limit stored in another table is done with a trigger, not with CHECK.

Add a BEFORE INSERT trigger on accounts: block negative balance.

Show example solution

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

Add BEFORE

CREATE TRIGGER check_balance
____ INSERT ON accounts

Show answer

CREATE TRIGGER check_balance
BEFORE INSERT ON accounts