SQL · Theory

Trigger

← All stacks

Theory

83/439

Trigger

Don’t start Trigger with jargon. Start with Trigger on Dev's board. That is the whole point.

Do Trigger once by hand. one Trigger step Dev can write from memory. Change one input. Say the new result out loud.

Trigger exists so Dev can keep Trigger on Dev's board correct in a viva board.

Dev ships Trigger in a viva board. That is the use case worth saying.

Trigger miss — skipping the failure case for Trigger. Fix it before you talk about advanced SQL.

After Trigger, Dev should still remember skipping the failure case for Trigger.

Viva for Trigger: what it is → one Trigger step Dev can write from memory → the mistake (skipping the failure case for Trigger).

Exam tip

Say Trigger in one breath, then one Trigger step Dev can write from memory, then skipping the failure case for Trigger.

Trigger — sample query

-- Trigger
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefTrigger — Dev uses it for Trigger on Dev's board in a viva board.
RuleTrigger → one Trigger step Dev can write from memory.
RememberTrigger + InnoDB (a viva board).
UseTrigger in a viva board (Trigger on Dev's board).
TrapTrigger — skipping the failure case for Trigger.
ExTrigger → Trigger on Dev's board.

-- Trigger CREATE TABLE demo ( id INTEGER PRIMARY KEY, label TEXT NOT NULL, qty INTEGER DEFAULT 0 ); INSERT INTO demo (id, label, qty) VALUES (1, 'alpha', 2), (2, 'beta', 5); SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Trigger

Trigger — sample query

Short notes

Questions

Trigger

Trigger — sample query

Short notes

Questions