SQL · Theory

Trigger

Theory

83/439

Trigger

A trigger is SQL that runs automatically on INSERT, UPDATE, or DELETE. CREATE TRIGGER trg BEFORE INSERT ON marks FOR EACH ROW SET NEW.score = IF(NEW.score < 0, 0, NEW.score); clamps negative scores.

BEFORE can change NEW values. AFTER sees the row already written. FOR EACH ROW is the MySQL student form. Don’t hide all business rules in triggers — apps become hard to debug.

OLD is the previous row on UPDATE/DELETE. NEW is the incoming row on INSERT/UPDATE.

Trap — trigger that INSERTs into the same table → loop. Trap: huge logic that belongs in the app.

On the example next to this theory: Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

BEFORE INSERT … FOR EACH ROW SET NEW.col = …

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 = automatic SQL on INSERT/UPDATE/DELETE.
RuleBEFORE can edit NEW. AFTER cannot. OLD vs NEW.
TrapRecursive triggers. Business logic only in triggers.

SQL · Theory

Trigger

← All stacks

Theory

83/439

Trigger

A trigger is SQL that runs automatically on INSERT, UPDATE, or DELETE. CREATE TRIGGER trg BEFORE INSERT ON marks FOR EACH ROW SET NEW.score = IF(NEW.score < 0, 0, NEW.score); clamps negative scores.

BEFORE can change NEW values. AFTER sees the row already written. FOR EACH ROW is the MySQL student form. Don’t hide all business rules in triggers — apps become hard to debug.

OLD is the previous row on UPDATE/DELETE. NEW is the incoming row on INSERT/UPDATE.

Trap — trigger that INSERTs into the same table → loop. Trap: huge logic that belongs in the app.

On the example next to this theory: Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

BEFORE INSERT … FOR EACH ROW SET NEW.col = …

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 = automatic SQL on INSERT/UPDATE/DELETE.
RuleBEFORE can edit NEW. AFTER cannot. OLD vs NEW.
TrapRecursive triggers. Business logic only in triggers.

Trigger

Trigger — sample query

Short notes

Questions

Trigger

Trigger — sample query

Short notes

Questions