טריגרים ב-SQLite: CREATE TRIGGER, BEFORE/AFTER ו-OLD/NEW

טריגר מריץ SQL אוטומטית

טריגר הוא בלוק SQL שמור שמופעל בכל פעם שאירוע מסוים קורה בטבלה מסוימת. כותבים אותו פעם אחת. SQLite דואג ל"מתי" בשבילכם.

הצורה:

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;

אף פעם לא כתבנו INSERT מפורש ל-price_history. הטריגר עשה את זה. כל עדכון מחיר עתידי יירשם באותה דרך, בין אם הוא מגיע מה-CLI, מסקריפט או מאפליקציה.

האנטומיה של CREATE TRIGGER

קראו את התחביר חלק אחר חלק:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- one or more statements
END;

• תזמון: BEFORE רץ לפני השינוי, AFTER רץ אחריו, INSTEAD OF מחליף אותו (רק ב-views).
• אירוע: איזו פעולה מפעילה אותו. UPDATE OF col1, col2 מצמצם עדכונים לעמודות מסוימות.
• טבלה: הטבלה שנמצאת במעקב.
• FOR EACH ROW: SQLite תומך רק בטריגרים ברמת השורה, אז זה מובלע. אפשר לכתוב את זה לשם הבהירות, זה לא משנה כלום.
• WHEN: תנאי אופציונלי. גוף הטריגר רץ רק אם הוא אמת.
• גוף: פקודה אחת או יותר בין BEGIN ל-END. כל אחת חייבת להסתיים בנקודה פסיק.

זה כל הדקדוק. רוב הטריגרים האמיתיים הם באורך חמש עד עשר שורות.

OLD ו-NEW: השורה שמשתנה

בתוך הגוף, שתי פסאודו שורות מאפשרות לראות את הנתונים:

• NEW: השורה הנכנסת. זמינה בטריגרים של INSERT ושל UPDATE.
• OLD: השורה הקיימת. זמינה בטריגרים של UPDATE ושל DELETE.

לטריגר של DELETE יש רק OLD. לטריגר של INSERT יש רק NEW. לטריגר של UPDATE יש את שניהם.

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;

השורה שנמחקה כבר לא נמצאת ב-accounts, אבל הנתונים שלה נשמרו ב-deletions לפני שהיא נעלמה.

BEFORE: אימות או תיקון של השורה

טריגרים של BEFORE רצים לפני שהשינוי בשורה מגיע לדיסק. הם נוחים להעלאת שגיאה או לנרמול נתונים:

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, just illustrating context
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;

ה-INSERT השני נעצר לפני שנכתבת שורה כלשהי. RAISE(ABORT, '...') מבטל את הפקודה הנוכחית ומחזיר לתחילתה. RAISE(FAIL, ...), RAISE(ROLLBACK, ...) ו-RAISE(IGNORE) נותנים שליטה עדינה יותר במה שקורה.

לאימות נתונים טהור, העדיפו אילוצי CHECK: הם הצהרתיים והאופטימייזר מכיר אותם. פנו לטריגר BEFORE כשהכלל צריך להסתכל על טבלאות אחרות או לעשות משהו ש-CHECK לא יכול לבטא.

WHEN: טריגרים מותנים

פסוקית WHEN מסננת אילו שינויים בשורות באמת מפעילים את הגוף. היא מחושבת לכל שורה, אחרי ש-OLD ו-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;

ההזמנה הראשונה לא עוברת את הסף. השתיים האחרות כן. בלי פסוקית ה-WHEN, כל הכנסה הייתה נכתבת ל-big_orders והייתם צריכים לסנן בצד הקריאה.

INSTEAD OF: לאפשר כתיבה ל-view

Views הם לקריאה בלבד כברירת מחדל. טריגר INSTEAD OF מיירט כתיבה ל-view ומריץ את ה-SQL שלכם במקומה, בדרך כלל על ידי תרגום שלה לכתיבות לטבלה או לטבלאות שמתחת:

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;

האפליקציה מדברת עם ה-view כאילו הוא טבלה. הטריגר מטפל מאחורי הקלעים בפיצול ל-first_name ול-last_name.

הצגה ומחיקה של טריגרים

טריגרים נשמרים ב-sqlite_master לצד טבלאות ואינדקסים:

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

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- placeholder body
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; היא הצורה הבטוחה. מחיקת הטבלה שהטריגר שייך לה מוחקת את הטריגר אוטומטית: לא צריך לנקות קודם.

מלכודות שכדאי להכיר

כמה דברים נושכים אנשים בפעם הראשונה:

• טריגרים מופעלים לכל שורה, לא לכל פקודה. UPDATE שנוגע ב-1,000 שורות מפעיל את הטריגר 1,000 פעמים. אם הגוף עצמו יקר, זה מצטבר מהר.
• טריגרים רצים בתוך הטרנזקציה שעוטפת אותם. אם הפקודה החיצונית מבוטלת, גם הכתיבות של הטריגר מבוטלות. זה בדרך כלל מה שרוצים, אבל זה אומר שטריגר הוא לא פתח מילוט ל"תרשום את זה לא משנה מה".
• טריגרים רקורסיביים כבויים כברירת מחדל. טריגר שמשנה את אותה טבלה לא יפעיל את עצמו שוב, אלא אם תגדירו PRAGMA recursive_triggers = ON;. השאירו את זה כבוי אלא אם יש לכם סיבה מסוימת.
• כתיבות מצד האפליקציה יכולות לעקוף אותם, אבל רק אם מדלגים על מסד הנתונים. כל עוד כל כתיבה עוברת דרך SQLite, הטריגר ירוץ. גם ORM שמריץ אצוות דרך SQL גולמי מפעיל אותם.
• אל תפזרו לוגיקה עסקית על פני טריגרים רבים. הם בלתי נראים ממקום הקריאה: מי שמנסה להבין "למה השורה הזו הופיעה?" צריך לחפש ב-sqlite_master. השתמשו בהם לנושאים רוחביים (יומני ביקורת, עמודות נגזרות, כתיבה ל-views) והשאירו את השאר בקוד האפליקציה.

דוגמה מציאותית ליומן ביקורת

מחברים את התבניות יחד: מעקב אחרי כל שינוי בטבלת 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;

טריגר אחד שומר על updated_at מדויק וכותב שורת ביקורת במקום אחד. קוד האפליקציה שמבצע את ה-UPDATE לא צריך לדעת שאף אחד מהדברים האלה קיים.

הבא בתור: תמיכה ב-JSON

טריגרים מטפלים באוטומציה סביב אירועים בשורות. החלק הבא של SQLite המתקדם הוא מה שאפשר לשמור בתוך שורה: JSON. ל-SQLite יש סט מלא של פונקציות JSON לשאילתות ולעדכון של נתונים מובנים בלי לצאת מ-SQL, וזה הדף הבא.