Triggers: automating reactions

What a trigger is

A trigger is a block of SQL that the database runs on its own, automatically, when an event happens on a table: an INSERT, an UPDATE or a DELETE. It serves to maintain derived data, record audits or apply rules without relying on the application remembering to do it.

CREATE TRIGGER trigger_name
AFTER INSERT ON sales
BEGIN
  -- SQL to run when a row is inserted into sales
END;

AFTER / BEFORE and the event

• AFTER runs after the change is already done; BEFORE, just before. To audit what happened, the usual choice is AFTER.
• The event can be INSERT, UPDATE or DELETE ON a table.
• In SQLite the trigger fires once for each row affected (it is implicitly FOR EACH ROW).

The NEW and OLD rows

Inside the body you have two "virtual rows" to read the values in play:

CREATE TRIGGER log_sales
AFTER INSERT ON sales
BEGIN
  INSERT INTO audit (action, sale_id, amount)
  VALUES ('insert', NEW.id, NEW.amount);
END;

So, every time a sale comes in, the trigger automatically writes a line in audit with the data from NEW. To check its effect, the last statement queries the table the trigger has filled:

INSERT INTO sales (id, product_id, day, amount) VALUES (7, 2, 4, 99);
SELECT action, sale_id, amount FROM audit ORDER BY sale_id;

Triggers are powerful but invisible: since they act on their own, it is wise to name them clearly and document them so the team knows they exist.

Examples

Audit each new sale with an AFTER INSERT trigger

CREATE TABLE audit (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  action TEXT,
  sale_id INTEGER,
  amount REAL
);

CREATE TRIGGER log_sales
AFTER INSERT ON sales
BEGIN
  INSERT INTO audit (action, sale_id, amount)
  VALUES ('insert', NEW.id, NEW.amount);
END;

INSERT INTO sales (id, product_id, day, amount) VALUES (7, 2, 4, 99);
SELECT action, sale_id, amount FROM audit ORDER BY sale_id;