Trigger trong SQLite

Các Trigger trong SQLite là các hàm callback, mà tự động được thực hiện/triệu hồi khi một xuất hiện một sự kiện trong cơ sở dữ liệu. Sau đây là một số điểm quan trọng về Trigger trong SQLite:

• Trigger trong SQLite có thể được xác định để kích hoạt bất cứ khi nào một DELETE, INSERT hoặc UPDATE của một bảng dữ liệu cụ thể hoặc bất cứ khi nào một UPDATE xuất hiện trên một hoặc một số cột cụ thể trông một bảng.
• Tại thời điểm này, SQLite chỉ hỗ trợ các Trigger dạng FOR EACH ROW, không hỗ trợ Trigger dạng FOR EACH STATEMENT. Vì thế, việc xác định FOR EACH ROW là tùy ý.
• Cả mệnh đề WHERE và các hành động Trigger có thể truy cập các phần tử của hàng đang được chèn, đang bị xóa hoặc được cập nhật bởi sử dụng các tham chiếu theo mẫu NEW.column-name và OLD.column-name, với column-name là tên một cột từ bảng mà Trigger được gắn kết với.
• Nếu cung cấp một mệnh đề WHERE, các lệnh SQLite đã xác định đó chỉ được thực thi cho các hàng mà mệnh đề WHERE đó là true. Nếu không có mệnh đề WHERE nào, các lệnh SQLite sẽ được thực thi cho tất cả các hàng.
• Từ khóa BEFORE và AFTER xác định khi nào hành động Trigger sẽ được thực thi.
• Các Trigger tự động bị xóa khi bảng, mà chúng được gắn kết với, bị xóa.
• Bảng để sửa đổi phải tồn tại trong cùng cơ sở dữ liệu với bảng hoặc view mà Trigger được gắn kết.
• Một hàm SQLite đặc biệt là hàm RAISE() có thể được sử dụng bên trong một Trigger để tạo một ngoại lệ (exception).

Cú pháp cơ bản để tạo một Trigger trong SQLite là như sau:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Ở đây, event_name có thể là INSERT, DELETE và UPDATE trên bảng table_name. Bạn có thể xác định FOR EACH ROW một cách tùy ý sau tên bảng.

Sau đây là cú pháp để tạo một Trigger trên một hoạt động UPDATE trên một hoặc nhiều cột cụ thể của một bảng:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Ví dụ

Giả sử một tình huống khi bạn muốn dùng tính năng Audit Trial (kiểm tra dấu vết) cho mỗi bản ghi đang được chèn trong bảng COMPANY được tạo mới như dưới đây (xóa bảng này nếu bạn đã có).

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Để thực hiện Audit Trial, chúng ta sẽ tạo một bảng mới là AUDIT, với các thông báo log sẽ được chèn vào bất cứ khi nào một entry tạo một bản ghi mới vào bảng COMPANY này.

sqlite> CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

Ở đây, ID là Record ID của bảng AUDIT, và EMP_ID là ID mà đến từ bảng COMPANY và DATE sẽ giữ Timestamp khi bản ghi được tạo trong bảng COMPANY. Bây giờ, chúng ta tạo một Trigger trên bảng COMPANY này như sau:

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Lúc này, chúng ta sẽ bắt đầu công việc thực sự: bắt đầu chèn bản ghi vào trong bảng COMPANY và sẽ cho kết quả là tạo một bản ghi audit log trong bảng AUDIT. Giả sử chúng ta tạo bản ghi như sau:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Trong bảng COMPANY, ta sẽ có bản ghi sau:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

Cùng lúc đó, một bản ghi sẽ được tạo trong bảng AUDIT. Bản ghi này là kết quả của một Trigger, mà chúng ta đã tạo trên hoạt động INSERT trên bảng COMPANY. Tương tự, bạn có thể tạo các Trigger trên các hoạt động UPDATE và DELETE tùy theo yêu cầu của bạn.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

Liệt kê Trigger trong SQLite

Bạn có thể liệt kê các Trigger từ bảng sqlite_masert như sau:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

Lệnh trên sẽ chỉ liệt kê một entry là:

name
----------
audit_log

Nếu bạn muốn liệt kê các Trigger trên một bảng cụ thể, bạn sử dụng mệnh đề AND với tên bảng, như sau:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

Lệnh trên sẽ chỉ liệt kê một entry là:

name
----------
audit_log

Xóa Trigger trong SQLite

Lệnh DROP trong SQLite có thể được sử dụng để xóa một Trigger đang tồn tại.

sqlite> DROP TRIGGER trigger_name;