Trigger SQLite: CREATE TRIGGER, BEFORE/AFTER và OLD/NEW

Trigger tự động chạy SQL

Trigger là một khối SQL được lưu sẵn, chạy mỗi khi một sự kiện cụ thể xảy ra trên một bảng cụ thể. Bạn viết nó một lần. Còn chuyện "khi nào" thì SQLite lo.

Trông nó như thế này:

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;

Ta chưa hề viết lệnh INSERT nào vào price_history. Trigger đã làm việc đó. Mọi thay đổi giá về sau cũng sẽ được ghi lại theo cách này, dù nó đến từ CLI, một script hay ứng dụng.

Cấu trúc của CREATE TRIGGER

Đọc cú pháp từng phần một:

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;

• Thời điểm: BEFORE chạy trước thay đổi, AFTER chạy sau, INSTEAD OF chạy thay cho nó (chỉ với view).
• Sự kiện: thao tác kích hoạt trigger. UPDATE OF col1, col2 giới hạn việc update vào các cột cụ thể.
• Bảng: bảng được theo dõi.
• FOR EACH ROW: SQLite chỉ hỗ trợ trigger cấp hàng, nên điều này luôn được ngầm hiểu. Bạn có thể ghi ra cho rõ ràng; không có gì thay đổi.
• WHEN: điều kiện tùy chọn. Thân trigger chỉ chạy khi điều kiện đúng.
• Thân: một hoặc nhiều câu lệnh nằm giữa BEGIN và END. Mỗi câu lệnh phải kết thúc bằng dấu chấm phẩy.

Ngữ pháp chỉ có vậy. Phần lớn trigger thực tế chỉ dài từ năm đến mười dòng.

OLD và NEW: hàng đang bị thay đổi

Bên trong thân trigger, hai hàng giả cho phép bạn nhìn vào dữ liệu:

• NEW: hàng mới đi vào. Có trong trigger INSERT và UPDATE.
• OLD: hàng đang có. Có trong trigger UPDATE và DELETE.

Trigger DELETE chỉ có OLD. Trigger INSERT chỉ có NEW. Trigger UPDATE có cả hai.

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;

Hàng bị xóa đã biến mất khỏi accounts, nhưng dữ liệu của nó kịp được ghi vào deletions trước khi mất đi.

BEFORE: kiểm tra hoặc chỉnh sửa hàng

Trigger BEFORE chạy trước khi thay đổi trên hàng được ghi xuống đĩa. Nó tiện để báo lỗi hoặc chuẩn hóa dữ liệu:

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;

Lệnh INSERT thứ hai bị hủy trước khi có hàng nào được ghi. RAISE(ABORT, '...') hủy câu lệnh hiện tại và rollback về lúc câu lệnh bắt đầu. RAISE(FAIL, ...), RAISE(ROLLBACK, ...) và RAISE(IGNORE) cho bạn kiểm soát chi tiết hơn những gì xảy ra.

Với việc kiểm tra dữ liệu thuần túy, hãy ưu tiên ràng buộc CHECK: nó mang tính khai báo và trình tối ưu hiểu được nó. Hãy dùng trigger BEFORE khi quy tắc cần nhìn sang bảng khác hoặc làm điều mà CHECK không diễn đạt được.

WHEN: trigger có điều kiện

Mệnh đề WHEN lọc xem thay đổi nào trên hàng thực sự chạy thân trigger. Nó được tính cho từng hàng, sau khi OLD và NEW đã có giá trị:

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;

Đơn hàng đầu tiên không đạt điều kiện. Hai đơn còn lại thì đạt. Nếu không có mệnh đề WHEN, mỗi lần insert đều sẽ ghi vào big_orders và bạn phải lọc lại khi đọc.

INSTEAD OF: giúp ghi được vào view

Mặc định, view chỉ đọc được. Trigger INSTEAD OF chặn lệnh ghi vào view và chạy SQL của bạn thay vào đó, thường là chuyển nó thành lệnh ghi vào các bảng phía sau:

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;

Ứng dụng làm việc với view như thể đó là một bảng. Trigger lo việc tách tên thành first_name và last_name ở phía sau.

Liệt kê và xóa trigger

Trigger được lưu trong sqlite_master, cùng với bảng và index:

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; là cách viết an toàn. Xóa bảng chứa trigger sẽ tự động xóa trigger đó, nên bạn không cần dọn dẹp trước.

Những cái bẫy cần biết

Vài điều khiến nhiều người vấp phải ở lần đầu:

• Trigger chạy theo từng hàng, không theo từng câu lệnh. Một lệnh UPDATE chạm vào 1.000 hàng sẽ chạy trigger 1.000 lần. Nếu thân trigger tốn kém, chi phí sẽ cộng dồn rất nhanh.
• Trigger chạy bên trong transaction bao ngoài. Nếu câu lệnh bên ngoài bị rollback, các lệnh ghi của trigger cũng bị rollback theo. Thường thì đó là điều bạn muốn, nhưng nó cũng có nghĩa trigger không phải lối thoát để "ghi lại dù có chuyện gì xảy ra".
• Recursive trigger bị tắt theo mặc định. Một trigger sửa chính bảng của nó sẽ không tự kích hoạt lại, trừ khi bạn đặt PRAGMA recursive_triggers = ON;. Cứ để tắt, trừ khi bạn có lý do cụ thể.
• Lệnh ghi từ ứng dụng chỉ có thể bỏ qua trigger nếu không đi qua cơ sở dữ liệu. Chừng nào mọi lệnh ghi đều đi qua SQLite, trigger sẽ chạy. Một ORM gửi batch bằng SQL thô vẫn kích hoạt chúng.
• Đừng rải logic nghiệp vụ ra nhiều trigger. Trigger vô hình ở chỗ gọi lệnh: ai đó muốn biết "sao hàng này lại xuất hiện?" sẽ phải lục lọi sqlite_master. Dùng trigger cho các mối quan tâm xuyên suốt (log kiểm tra, cột dẫn xuất, view ghi được) và để phần còn lại trong code ứng dụng.

Ví dụ log kiểm tra thực tế

Kết hợp các mẫu ở trên: theo dõi mọi thay đổi trên bảng 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;

Một trigger vừa giữ updated_at luôn chính xác vừa ghi một hàng kiểm tra, tất cả ở cùng một chỗ. Code ứng dụng chạy lệnh UPDATE không cần biết hai việc đó đang diễn ra.

Tiếp theo: hỗ trợ JSON

Trigger lo phần tự động hóa quanh các sự kiện trên hàng. Phần SQLite nâng cao tiếp theo là những gì bạn có thể lưu bên trong một hàng: JSON. SQLite có đầy đủ các hàm JSON để truy vấn và cập nhật dữ liệu có cấu trúc mà không cần rời khỏi SQL, và đó là nội dung của trang tiếp theo.