Trigger in SQLite: CREATE TRIGGER, BEFORE/AFTER e OLD/NEW

Un trigger esegue SQL in automatico

Un trigger è un blocco di SQL memorizzato che si attiva ogni volta che su una certa tabella avviene un certo evento. Lo scrivi una volta sola. Al "quando" ci pensa SQLite.

La forma:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Notebook', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Notebook';
SELECT * FROM price_history;

Non abbiamo mai scritto un INSERT esplicito in price_history. L'ha fatto il trigger. Ogni futuro aggiornamento del prezzo verrà registrato allo stesso modo, che arrivi dalla CLI, da uno script o da un'app.

Anatomia di CREATE TRIGGER

Leggi la sintassi pezzo per pezzo:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- una o più istruzioni
END;

• Momento: BEFORE viene eseguito prima della modifica, AFTER dopo, INSTEAD OF la sostituisce (solo sulle viste).
• Evento: quale operazione lo attiva. UPDATE OF col1, col2 restringe gli aggiornamenti a colonne specifiche.
• Tabella: la tabella osservata.
• FOR EACH ROW: SQLite supporta solo trigger a livello di riga, quindi è implicito. Puoi scriverlo per chiarezza; non cambia nulla.
• WHEN: una condizione facoltativa. Il corpo del trigger viene eseguito solo se è vera.
• Corpo: una o più istruzioni tra BEGIN ed END. Ognuna deve terminare con un punto e virgola.

Questa è tutta la grammatica. La maggior parte dei trigger reali occupa da cinque a dieci righe.

OLD e NEW: la riga che cambia

Dentro il corpo, due pseudo righe ti fanno vedere i dati:

• NEW: la riga in arrivo. Disponibile nei trigger INSERT e UPDATE.
• OLD: la riga esistente. Disponibile nei trigger UPDATE e DELETE.

Un trigger DELETE ha solo OLD. Un trigger INSERT ha solo NEW. Un trigger UPDATE li ha entrambi.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

La riga eliminata non c'è più in accounts, ma i suoi dati sono stati salvati in deletions prima che sparisse.

BEFORE: validare o sistemare la riga

I trigger BEFORE vengono eseguiti prima che la modifica della riga arrivi su disco. Sono comodi per sollevare un errore o normalizzare i dati:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, serve solo a illustrare il contesto
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'email cannot be empty');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

Il secondo INSERT viene interrotto prima che venga scritta qualsiasi riga. RAISE(ABORT, '...') annulla l'istruzione corrente e fa rollback fino al suo inizio; RAISE(FAIL, ...), RAISE(ROLLBACK, ...) e RAISE(IGNORE) ti danno un controllo più fine su cosa succede.

Per la pura validazione dei dati, preferisci i vincoli CHECK: sono dichiarativi e l'ottimizzatore li conosce. Ricorri a un trigger BEFORE quando la regola deve guardare altre tabelle o fare qualcosa che un CHECK non riesce a esprimere.

WHEN: trigger condizionali

Una clausola WHEN filtra quali modifiche di riga attivano davvero il corpo. Viene valutata per ogni riga, dopo che OLD e NEW sono stati associati:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

Il primo ordine non passa la soglia. Gli altri due sì. Senza la clausola WHEN, ogni insert avrebbe scritto in big_orders e dovresti filtrare in lettura.

INSTEAD OF: rendere scrivibile una vista

Le viste per impostazione predefinita sono in sola lettura. Un trigger INSTEAD OF intercetta una scrittura su una vista ed esegue il tuo SQL al suo posto, di solito traducendola in scritture sulle tabelle sottostanti:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

L'applicazione parla con la vista come se fosse una tabella. Il trigger si occupa dietro le quinte di dividere il nome in first_name e last_name.

Elencare ed eliminare i trigger

I trigger stanno in sqlite_master insieme a tabelle e indici:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- corpo segnaposto
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; è la forma sicura. Eliminare la tabella su cui si trova un trigger elimina automaticamente anche il trigger: non serve fare pulizia prima.

Insidie da conoscere

Alcune cose fanno male la prima volta:

• I trigger si attivano per riga, non per istruzione. Un UPDATE che tocca 1.000 righe attiva il trigger 1.000 volte. Se il corpo stesso è costoso, il conto sale in fretta.
• I trigger vengono eseguiti dentro la transazione circostante. Se l'istruzione esterna fa rollback, anche le scritture del trigger vengono annullate. Di solito è quello che vuoi, ma significa che un trigger non è una scappatoia per "registra questo in ogni caso".
• I trigger ricorsivi sono disattivati per impostazione predefinita. Un trigger che modifica la stessa tabella non si riattiva da solo, a meno che tu non imposti PRAGMA recursive_triggers = ON;. Lascialo disattivato se non hai un motivo preciso.
• Le scritture lato applicazione possono aggirarli, ma solo saltando il database. Finché ogni scrittura passa da SQLite, il trigger verrà eseguito. Anche gli ORM che raggruppano le operazioni con SQL grezzo li attivano.
• Non sparpagliare la logica di business su tanti trigger. Sono invisibili dal punto di chiamata: chi sta cercando di capire "perché è comparsa questa riga?" deve andare a frugare in sqlite_master. Usali per le questioni trasversali (log di audit, colonne derivate, viste scrivibili) e tieni il resto nel codice dell'applicazione.

Un esempio realistico di log di audit

Mettiamo insieme gli schemi visti: tracciare ogni modifica a una tabella posts:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Draft', 'first cut');
UPDATE posts SET title = 'Published' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

Un solo trigger tiene aggiornato updated_at e scrive una riga di audit, tutto in un unico punto. Il codice dell'applicazione che esegue l'UPDATE non ha bisogno di sapere che nessuna delle due cose esiste.

Prossimo passo: supporto JSON

I trigger gestiscono l'automazione attorno agli eventi sulle righe. Il prossimo tassello di SQLite avanzato è quello che puoi memorizzare dentro una riga: il JSON. SQLite ha un set completo di funzioni JSON per interrogare e aggiornare dati strutturati senza uscire da SQL, ed è la prossima pagina.