Bài 12: Trigger, View & Virtual Table

Bài 11 dạy ràng buộc (CHECK/UNIQUE/FOREIGN KEY) để chặn dữ liệu sai. Bài này đi xa hơn: làm sao để database tự động phản ứng mỗi khi dữ liệu thay đổi (trigger), làm sao "gói" 1 câu truy vấn phức tạp thành 1 "bảng ảo" dùng lại được (view), và SQLite thực chất còn cho phép cả 1 bảng hoàn toàn không lưu dữ liệu theo cách thông thường — mọi thao tác SELECT/INSERT lên nó đều do 1 đoạn mã tuỳ biến xử lý phía sau (virtual table) — chính là cơ chế đứng sau tìm kiếm toàn văn (FTS) và nhiều tính năng "ma thuật" khác của SQLite.

1. Trigger BEFORE/AFTER: Audit Log Tự Động

Trigger là 1 khối lệnh SQL tự động chạy mỗi khi 1 sự kiện xảy ra trên bảng (INSERT/UPDATE/DELETE) — không cần ứng dụng gọi thêm bất kỳ câu lệnh nào. Tình huống kinh doanh: ngân hàng bắt buộc phải lưu vết mọi thay đổi số dư (audit trail) để phục vụ kiểm toán, dù lập trình viên ứng dụng có quên gọi hàm ghi log hay không:

CREATE TRIGGER trg_audit_balance
AFTER UPDATE OF balance ON bank_accounts
FOR EACH ROW
BEGIN
  INSERT INTO balance_audit_log (account_id, old_balance, new_balance, changed_at)
  VALUES (OLD.account_id, OLD.balance, NEW.balance, datetime('now'));
END;

UPDATE bank_accounts SET balance = balance - 300 WHERE account_id = 1;
-- Không cần thêm câu lệnh nào khác — balance_audit_log đã TỰ ĐỘNG có
-- đúng 1 dòng: account_id=1, old_balance=1000, new_balance=700

OLD và NEW là 2 "bí danh" đặc biệt chỉ dùng được bên trong trigger: OLD trỏ tới giá trị dòng trước thay đổi, NEW trỏ tới giá trị sau thay đổi. Với DELETE, chỉ có OLD (dòng sắp biến mất); với INSERT, chỉ có NEW (dòng sắp được tạo).

Trigger không chỉ ghi log — còn thực thi được ràng buộc phức tạp hơn CHECK cho phép bằng RAISE(ABORT, 'thông báo lỗi tuỳ biến'). Ví dụ: chặn tên chủ tài khoản chỉ toàn khoảng trắng (CHECK đơn thuần khó diễn đạt gọn "không được rỗng SAU KHI trim khoảng trắng"):

CREATE TRIGGER trg_reject_blank_name
BEFORE INSERT ON bank_accounts
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'owner_name không được để trống')
  WHERE length(trim(NEW.owner_name)) = 0;
END;

INSERT INTO bank_accounts VALUES (3, '   ', 200);
-- → Lỗi: owner_name không được để trống (đúng như trigger định nghĩa)

Tình huống thứ 3: giữ lại dữ liệu trước khi nó biến mất — trigger AFTER DELETE ghi thông tin dòng sắp xoá vào 1 bảng log riêng, phục vụ khôi phục/kiểm toán sau này (khác ROLLBACK ở Bài 11: đây là lưu vết vĩnh viễn sau khi transaction đã COMMIT thật):

CREATE TRIGGER trg_log_delete
AFTER DELETE ON bank_accounts
FOR EACH ROW
BEGIN
  INSERT INTO deleted_accounts_log VALUES (OLD.account_id, OLD.owner_name, datetime('now'));
END;

DELETE FROM bank_accounts WHERE account_id = 3;
-- Tài khoản 3 đã mất khỏi bank_accounts, nhưng deleted_accounts_log
-- vẫn còn đúng tên chủ tài khoản + thời điểm xoá

2. View: Lớp Trừu Tượng Trên Dữ Liệu

View là 1 câu truy vấn được đặt tên và lưu lại, dùng như 1 bảng bình thường trong mọi câu lệnh khác — nhưng không hề lưu trữ dữ liệu riêng, chỉ chạy lại đúng câu SELECT gốc mỗi lần được tham chiếu:

CREATE VIEW account_summary AS
SELECT account_id, owner_name, balance FROM bank_accounts;

SELECT * FROM account_summary WHERE balance > 600; -- dùng như bảng thật

Lợi ích thực tế: ẩn độ phức tạp (1 view có thể gói gọn 3-4 bảng JOIN phức tạp như orders/order_items/products ở Bài 3 thành 1 "bảng" đơn giản cho báo cáo), và giới hạn quyền truy cập theo cột (1 view chỉ chọn owner_name, balance, ẩn hẳn cột nhạy cảm như email khỏi những ai chỉ được cấp quyền đọc view đó, dù bảng gốc vẫn còn nguyên cột email).

Điểm quan trọng hay bị hiểu nhầm: view mặc định chỉ đọc. Thử ghi trực tiếp:

UPDATE account_summary SET balance = 5000 WHERE account_id = 2;
-- → Lỗi: cannot modify account_summary because it is a view

Muốn view "ghi được" (updatable view), cần INSTEAD OF trigger — chặn đúng thao tác ghi trên view, rồi tự viết lại nó thành thao tác ghi lên bảng gốc thật:

CREATE TRIGGER trg_update_via_view
INSTEAD OF UPDATE ON account_summary
FOR EACH ROW
BEGIN
  UPDATE bank_accounts SET balance = NEW.balance WHERE account_id = NEW.account_id;
END;

UPDATE account_summary SET balance = 5000 WHERE account_id = 2;
-- Giờ CHẠY ĐƯỢC — INSTEAD OF trigger âm thầm chuyển thành
-- UPDATE bank_accounts thật, account_id=2 giờ có balance=5000

Đây chính xác là cách nhiều framework ORM (Object-Relational Mapping) hiện đại triển khai "updatable view" hoặc bảng ảo phục vụ tương thích ngược khi đổi cấu trúc bảng gốc mà không muốn phá vỡ code cũ đang query theo cấu trúc bảng trước đây.

3. Cơ Chế Virtual Table: Bảng "Giả" Đứng Sau FTS3/FTS5

Virtual table đi xa hơn view: nó không chạy lại 1 câu SELECT trên bảng thật có sẵn — toàn bộ hành vi SELECT/INSERT/UPDATE lên nó do 1 module mở rộng tự định nghĩa (viết bằng C, tuân theo giao diện xCreate, xBestIndex, xFilter, xColumn, xNext...). Với người dùng SQL, nó vẫn trông y hệt 1 bảng bình thường — đây chính là "ma thuật" đứng sau nhiều tính năng nổi tiếng của SQLite: FTS3/FTS4/FTS5 (tìm kiếm toàn văn qua inverted index, không phải bảng B-Tree thông thường — xem lại Bài 10), R-Tree (index không gian cho toạ độ địa lý), hay CSV virtual table (query thẳng 1 file .csv như thể nó là bảng SQL).

Tạo virtual table FTS3 tìm kiếm toàn văn trên tên + danh mục sản phẩm TechMart:

CREATE VIRTUAL TABLE product_search USING fts3(product_name, category);

INSERT INTO product_search (product_name, category)
SELECT product_name, category FROM products;

SELECT * FROM product_search WHERE product_search MATCH 'wireless';
-- → Wireless Mouse | Accessories

Cú pháp SELECT ... FROM product_search trông y hệt bảng thường — nhưng bên dưới, product_search không hề dùng cấu trúc B-Tree đã học ở Bài 10: dữ liệu được tổ chức thành inverted index (ánh xạ ngược: mỗi từ → danh sách hàng chứa từ đó), cho phép MATCH tìm ra kết quả gần như tức thì kể cả trên hàng triệu dòng văn bản — điều LIKE '%wireless%' thông thường không thể làm nhanh được (buộc phải quét toàn bảng, xem lại Bài 8).

4. Mở Rộng SQLite Từ JavaScript: Hàm SQL Tuỳ Biến

sql.js không cho phép tự viết 1 module virtual table hoàn chỉnh bằng JavaScript (giao diện xCreate/xBestIndex... chỉ triển khai được ở tầng C khi biên dịch SQLite) — nhưng nó mở 1 cánh cửa khác để "tiêm" logic JavaScript thẳng vào câu SQL: db.create_function(). Đây đăng ký 1 hàm SQL tuỳ biến, gọi được trong bất kỳ câu lệnh nào y hệt hàm built-in như date()/typeof() đã dùng ở Bài 1:

db.create_function('format_vnd', (amount) => {
  if (amount === null) return null;
  return Number(amount).toLocaleString('vi-VN') + ' đ';
});

SELECT order_id, total_amount, format_vnd(total_amount) FROM orders LIMIT 5;
-- order_id=1  total_amount=56.97   format_vnd=56,97 đ
-- order_id=2  total_amount=249.99  format_vnd=249,99 đ
-- order_id=3  total_amount=219.98  format_vnd=219,98 đ
-- order_id=4  total_amount=79.99   format_vnd=79,99 đ
-- order_id=5  total_amount=45.00   format_vnd=45 đ

toLocaleString('vi-VN') tự động đổi dấu thập phân sang dấu phẩy đúng quy ước Việt Nam (249.99 → "249,99") — hoàn toàn không thể làm bằng SQL thuần trong SQLite (không có hàm định dạng locale sẵn), nhưng chỉ mất 3 dòng JavaScript. Đây là ví dụ thực tế cho việc mở rộng vốn từ vựng SQL bằng logic ứng dụng — khác cơ chế virtual table (thay thế toàn bộ cách 1 "bảng" hoạt động) nhưng cùng tinh thần: SQLite được thiết kế để mở rộng được, không phải 1 hộp đen đóng kín.

Sân Chơi Tương Tác: Trigger, View & Virtual Table

Bấm lần lượt 1️⃣ → 6️⃣ để tái tạo đúng mọi kết quả ở trên: audit log tự động, ràng buộc qua trigger, updatable view, tìm kiếm FTS3, và hàm SQL tuỳ biến.