meet it

A trigger can stop a bad change with .

Some rules are too clever for a CHECK, such as a price never rising by more than half in one go. A BEFORE trigger runs before the change happens, so it can compare OLD with NEW and refuse.

A WHEN condition after the table name makes the trigger fire only for bad changes. Inside, SELECT RAISE(ABORT, 'message') stops and undoes the statement, and your message becomes the error. RAISE(IGNORE) skips the bad row quietly instead.

Write messages that say plainly what went wrong.

CREATE TRIGGER fair_price BEFORE UPDATE OF price ON books
WHEN NEW.price > OLD.price * 1.5
BEGIN
  SELECT RAISE(ABORT, 'Price rise too big');
END;

UPDATE books SET price = 10 WHERE id = 1;
SELECT title, price FROM books WHERE id = 1;

From 8 to 10 is a fair rise, so the trigger stays quiet and the update goes through.

guess first

Doubling a price past the guard. What happens?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

CREATE TRIGGER fair_price BEFORE UPDATE OF price ON books
WHEN NEW.price > OLD.price * 1.5
BEGIN
  SELECT RAISE(ABORT, 'Price rise too big');
END;

UPDATE books SET price = price * 2 WHERE id = 2;
SELECT title, price FROM books WHERE id = 2;

your turn

Your turn. Never sell stock you do not have.

Every book loses one copy, but Pirate School has none left. Fill in the test and the kind of RAISE so any row that would go below zero is skipped quietly.

Your code

result

Your answer appears here as a table.

fix it

The guard itself has a syntax error. Fix it!

Broken code to fix

result

Your answer appears here as a table.

The error points at RAISE. What kind of statement can hold it?
RAISE works like a function, so inside a trigger it lives in a SELECT.
Write SELECT in front of RAISE on line 4, then press run.

nailed it

bad changes bounce off now. :D

A BEFORE trigger runs before the change and can stop it.
WHEN makes the trigger fire only for bad changes.
RAISE(ABORT, 'message') stops the change, RAISE(IGNORE) skips the row.

new words in your collection

BEFORE
RAISE

This lesson isn’t saved. A free account remembers every lesson you finish.

A BEFORE trigger can stop a bad change with RAISE.

Doubling a price past the guard. What happens?

Your turn. Never sell stock you do not have.

The guard itself has a syntax error. Fix it!

bad changes bounce off now. :D

A trigger can stop a bad change with .