Wyzwalacze w SQLite: CREATE TRIGGER, BEFORE/AFTER i…

Q: Czym różnią się wyzwalacze BEFORE, AFTER i INSTEAD OF?

BEFORE działa przed zastosowaniem zmiany wiersza: przydaje się do walidacji albo poprawiania danych. AFTER działa, gdy zmiana już nastąpiła: przydaje się do logowania albo synchronizacji innych tabel. INSTEAD OF działa tylko na widokach i całkowicie zastępuje planowaną operację, dzięki czemu widok staje się zapisywalny.

Q: Jak odwołać się do zmienianego wiersza wewnątrz wyzwalacza?

Użyj NEW.column dla nowego wiersza przy INSERT i UPDATE oraz OLD.column dla istniejącego wiersza przy UPDATE i DELETE. Wyzwalacze INSERT widzą tylko NEW, wyzwalacze DELETE tylko OLD, a wyzwalacze UPDATE oba. Te odwołania dotyczą wiersza, który jest właśnie przetwarzany.

Q: Jak wyświetlić lub usunąć wyzwalacze w SQLite?

Wyzwalacze są zapisane w sqlitemaster: SELECT name, tblname FROM sqlitemaster WHERE type = 'trigger'; pokazuje wszystkie. Aby usunąć jeden, użyj DROP TRIGGER triggername; albo DROP TRIGGER IF EXISTS trigger_name;, jeśli nie masz pewności, że istnieje. Usunięcie tabeli usuwa też jej wyzwalacze.

Wyzwalacz uruchamia SQL automatycznie

Wyzwalacz (trigger) to zapisany blok SQL, który odpala się za każdym razem, gdy na określonej tabeli zajdzie określone zdarzenie. Piszesz go raz. O to, „kiedy”, dba SQLite.

Tak to wygląda:

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;

Nigdzie nie napisaliśmy jawnego INSERT do price_history. Zrobił to wyzwalacz. Każda przyszła zmiana ceny zostanie zapisana w ten sam sposób, niezależnie od tego, czy pochodzi z CLI, skryptu czy aplikacji.

Budowa CREATE TRIGGER

Przeczytaj składnię kawałek po kawałku:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- jedna lub więcej instrukcji
END;

Moment: BEFORE działa przed zmianą, AFTER po niej, INSTEAD OF ją zastępuje (tylko widoki).
Zdarzenie: która operacja uruchamia wyzwalacz. UPDATE OF col1, col2 zawęża aktualizacje do konkretnych kolumn.
Tabela: obserwowana tabela.
FOR EACH ROW: SQLite obsługuje tylko wyzwalacze na poziomie wiersza, więc to jest domyślne. Możesz to dopisać dla czytelności; niczego nie zmienia.
WHEN: opcjonalny warunek. Treść wyzwalacza wykonuje się tylko wtedy, gdy jest prawdziwy.
Treść: jedna lub więcej instrukcji między BEGIN a END. Każda musi kończyć się średnikiem.

To cała gramatyka. Większość prawdziwych wyzwalaczy ma od pięciu do dziesięciu linijek.

OLD i NEW: zmieniany wiersz

W treści wyzwalacza dwa pseudowiersze pozwalają zobaczyć dane:

NEW: nowy wiersz. Dostępny w wyzwalaczach INSERT i UPDATE.
OLD: istniejący wiersz. Dostępny w wyzwalaczach UPDATE i DELETE.

Wyzwalacz DELETE ma tylko OLD. Wyzwalacz INSERT ma tylko NEW. Wyzwalacz UPDATE ma oba.

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;

Usunięty wiersz zniknął z accounts, ale jego dane zostały zapisane w deletions, zanim zniknął.

BEFORE: walidacja lub poprawka wiersza

Wyzwalacze BEFORE działają, zanim zmiana wiersza trafi na dysk. Przydają się do zgłoszenia błędu albo normalizacji danych:

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; -- nic nie robi, tylko ilustruje kontekst
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;

Drugi INSERT zostaje przerwany, zanim jakikolwiek wiersz zostanie zapisany. RAISE(ABORT, '...') anuluje bieżącą instrukcję i wycofuje zmiany do jej początku; RAISE(FAIL, ...), RAISE(ROLLBACK, ...) i RAISE(IGNORE) dają dokładniejszą kontrolę nad tym, co się dzieje.

Do czystej walidacji danych lepiej używać ograniczeń CHECK: są deklaratywne, a optymalizator o nich wie. Po wyzwalacz BEFORE sięgnij, gdy reguła musi zajrzeć do innych tabel albo zrobić coś, czego CHECK nie wyrazi.

WHEN: wyzwalacze warunkowe

Klauzula WHEN filtruje, które zmiany wierszy faktycznie uruchamiają treść wyzwalacza. Jest obliczana dla każdego wiersza, po powiązaniu OLD i NEW:

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;

Pierwsze zamówienie nie spełnia warunku. Dwa pozostałe tak. Bez klauzuli WHEN każde wstawienie trafiłoby do big_orders i filtrowanie musiałoby odbywać się przy odczycie.

INSTEAD OF: zapisywalny widok

Widoki domyślnie są tylko do odczytu. Wyzwalacz INSTEAD OF przechwytuje zapis do widoku i uruchamia zamiast niego twój SQL, zwykle tłumacząc go na zapisy do tabel źródłowych:

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;

Aplikacja rozmawia z widokiem tak, jakby był tabelą. Wyzwalacz w tle dzieli dane na first_name i last_name.

Wyświetlanie i usuwanie wyzwalaczy

Wyzwalacze są zapisane w sqlite_master razem z tabelami i indeksami:

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

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- tymczasowa treść
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; to bezpieczna forma. Usunięcie tabeli, do której należy wyzwalacz, automatycznie usuwa też wyzwalacz, więc nie musisz niczego sprzątać wcześniej.

Pułapki, o których warto wiedzieć

Kilka rzeczy zaskakuje ludzi za pierwszym razem:

Wyzwalacze odpalają się dla każdego wiersza, a nie dla każdej instrukcji. UPDATE, który zmienia 1000 wierszy, odpala wyzwalacz 1000 razy. Jeśli sama treść jest kosztowna, szybko się to sumuje.
Wyzwalacze działają w ramach otaczającej transakcji. Jeśli zewnętrzna instrukcja zostanie wycofana, zapisy wyzwalacza też. Zwykle o to chodzi, ale oznacza to, że wyzwalacz nie jest furtką typu „zaloguj to bez względu na wszystko”.
Wyzwalacze rekurencyjne są domyślnie wyłączone. Wyzwalacz, który modyfikuje tę samą tabelę, nie odpali się ponownie, dopóki nie ustawisz PRAGMA recursive_triggers = ON;. Zostaw to wyłączone, chyba że masz konkretny powód.
Zapisy z aplikacji mogą je ominąć tylko z pominięciem bazy. Dopóki każdy zapis przechodzi przez SQLite, wyzwalacz się uruchomi. ORM-y, które wysyłają paczki przez surowy SQL, też je uruchamiają.
Nie rozpraszaj logiki biznesowej po wielu wyzwalaczach. Nie widać ich w miejscu wywołania: ktoś, kto debuguje „skąd wziął się ten wiersz?”, musi przeszukiwać sqlite_master. Używaj ich do spraw przekrojowych (dzienniki audytu, kolumny pochodne, zapisywalne widoki), a resztę trzymaj w kodzie aplikacji.

Realistyczny przykład dziennika audytu

Łączymy wzorce: śledzimy każdą zmianę w tabeli 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;

Jeden wyzwalacz pilnuje, żeby updated_at było aktualne, i zapisuje wiersz audytu w jednym miejscu. Kod aplikacji, który wykonuje UPDATE, nie musi wiedzieć o żadnej z tych rzeczy.

Dalej: obsługa JSON

Wyzwalacze automatyzują reakcje na zdarzenia dotyczące wierszy. Kolejny element zaawansowanego SQLite to to, co możesz przechowywać wewnątrz wiersza, czyli JSON. SQLite ma pełny zestaw funkcji JSON do odpytywania i aktualizowania danych strukturalnych bez wychodzenia z SQL. O tym jest następna strona.

Najczęściej zadawane pytania

Czym jest wyzwalacz (trigger) w SQLite?

Wyzwalacz to blok SQL, który uruchamia się automatycznie, gdy na tabeli zajdzie określone zdarzenie: INSERT, UPDATE albo DELETE. Definiujesz go raz przez CREATE TRIGGER, a SQLite odpala go za ciebie przy każdym takim zdarzeniu. Tak prowadzi się dzienniki audytu, utrzymuje kolumny pochodne albo wymusza reguły bez polegania na tym, że aplikacja o nich pamięta.

Czym różnią się wyzwalacze BEFORE, AFTER i INSTEAD OF?

BEFORE działa przed zastosowaniem zmiany wiersza: przydaje się do walidacji albo poprawiania danych. AFTER działa, gdy zmiana już nastąpiła: przydaje się do logowania albo synchronizacji innych tabel. INSTEAD OF działa tylko na widokach i całkowicie zastępuje planowaną operację, dzięki czemu widok staje się zapisywalny.

Jak odwołać się do zmienianego wiersza wewnątrz wyzwalacza?

Użyj NEW.column dla nowego wiersza przy INSERT i UPDATE oraz OLD.column dla istniejącego wiersza przy UPDATE i DELETE. Wyzwalacze INSERT widzą tylko NEW, wyzwalacze DELETE tylko OLD, a wyzwalacze UPDATE oba. Te odwołania dotyczą wiersza, który jest właśnie przetwarzany.

Jak wyświetlić lub usunąć wyzwalacze w SQLite?

Wyzwalacze są zapisane w sqlite_master: SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger'; pokazuje wszystkie. Aby usunąć jeden, użyj DROP TRIGGER trigger_name; albo DROP TRIGGER IF EXISTS trigger_name;, jeśli nie masz pewności, że istnieje. Usunięcie tabeli usuwa też jej wyzwalacze.

Powiązane zagadnienia