CREATE TRIGGER în SQLite: explicație și exemple

Ce face `CREATE TRIGGER`

Un declanșator (trigger) este un bloc de instrucțiuni pe care SQLite îl rulează singur la un INSERT, UPDATE sau DELETE. În interior, NEW.coloana este valoarea nouă (la INSERT și UPDATE), iar OLD.coloana valoarea veche (la UPDATE și DELETE).

SQLite are doar declanșatori la nivel de rând: FOR EACH ROW este implicit și singura variantă (nu există FOR EACH STATEMENT). WHEN limitează declanșatorul la rândurile care îndeplinesc o condiție. UPDATE OF pret îl rulează doar când se schimbă coloana pret.

Funcția RAISE(), permisă doar în declanșatori, oprește operația: RAISE(ABORT, 'mesaj') anulează instrucțiunea curentă cu o eroare, RAISE(IGNORE) sare peste rândul curent fără eroare. Declanșatorii INSTEAD OF se pun pe vederi și le fac modificabile.

Parametri

Parametru	Descriere
`BEFORE \| AFTER`	Rulează înainte sau după modificarea rândului. Pentru validări folosește `BEFORE`, pentru jurnal `AFTER`.
`INSTEAD OF`	Doar pe vederi: rulează în locul operației, care altfel ar da eroare.
`WHEN conditie`	Declanșatorul rulează doar pentru rândurile care îndeplinesc condiția.
`NEW / OLD`	Rândul nou și rândul vechi, accesibile în interiorul declanșatorului.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE produse (
  id INTEGER PRIMARY KEY,
  nume TEXT NOT NULL,
  categorie TEXT,
  pret INTEGER,
  stoc INTEGER
);
INSERT INTO produse (nume, categorie, pret, stoc) VALUES
  ('Laptop', 'Electronice', 3500, 4),
  ('Telefon', 'Electronice', 2200, 10),
  ('Birou', 'Mobilier', 800, 3),
  ('Scaun', 'Mobilier', 450, 12),
  ('Caiet', 'Papetărie', 12, 200),
  ('Pix', 'Papetărie', 5, 500);

Exemplul 1: Jurnalul schimbărilor de preț

La fiecare schimbare a coloanei pret, declanșatorul scrie prețul vechi și pe cel nou într-un tabel de istoric. Schimbarea stocului nu îl declanșează.

CREATE TABLE istoric_preturi (produs_id INTEGER, pret_vechi INTEGER, pret_nou INTEGER);

CREATE TRIGGER jurnal_pret AFTER UPDATE OF pret ON produse
WHEN OLD.pret <> NEW.pret
BEGIN
  INSERT INTO istoric_preturi VALUES (OLD.id, OLD.pret, NEW.pret);
END;

UPDATE produse SET pret = 3200 WHERE nume = 'Laptop';
UPDATE produse SET stoc = 2 WHERE nume = 'Birou';
UPDATE produse SET pret = pret + 50 WHERE categorie = 'Mobilier';

SELECT p.nume, i.pret_vechi, i.pret_nou
FROM istoric_preturi i JOIN produse p ON p.id = i.produs_id
ORDER BY p.nume;

Rezultat

╭────────┬────────────┬──────────╮
│  nume  │ pret_vechi │ pret_nou │
╞════════╪════════════╪══════════╡
│ Birou  │        800 │      850 │
│ Laptop │       3500 │     3200 │
│ Scaun  │        450 │      500 │
╰────────┴────────────┴──────────╯

Exemplul 2: Validare cu RAISE

Declanșatorul refuză un produs cu prețul zero sau negativ. Mesajul din RAISE(ABORT, ...) devine mesajul erorii.

CREATE TRIGGER pret_pozitiv BEFORE INSERT ON produse
WHEN NEW.pret <= 0
BEGIN
  SELECT RAISE(ABORT, 'Prețul trebuie să fie pozitiv');
END;

INSERT INTO produse (nume, categorie, pret, stoc) VALUES ('Radieră', 'Papetărie', 0, 50);

Rezultat (eroare)

Error near line 7: Prețul trebuie să fie pozitiv

Exemplul 3: INSTEAD OF: o vedere în care se poate scrie

Vederea arată doar produsele de papetărie. Declanșatorul INSTEAD OF INSERT transformă inserarea în vedere într-o inserare în tabel, cu categoria completată automat.

CREATE VIEW papetarie AS
SELECT id, nume, pret FROM produse WHERE categorie = 'Papetărie';

CREATE TRIGGER papetarie_insert INSTEAD OF INSERT ON papetarie
BEGIN
  INSERT INTO produse (nume, categorie, pret, stoc)
  VALUES (NEW.nume, 'Papetărie', NEW.pret, 0);
END;

INSERT INTO papetarie (nume, pret) VALUES ('Radieră', 3);
SELECT nume, pret FROM papetarie ORDER BY pret;