ìm hiểu Trigger trong SQLite - SQLite căn bản và nâng cao

Trong bài học hôm nay mình sẽ giới thiệu một khái niệm khá quen thuộc trong SQLite đó trigger. Vậy trigger trong SQLite là gì? Nó được sử dụng trong SQLite như thế nào. Chúng ta cùng tìm hiểu trong nội dung tiếp theo.

1. Trigger trong SQLite là gì?

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

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

2. Cú pháp của trigger trong SQLite

Sau đây là cú pháp cơ bản của việc tạo ra một trigger.

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

Ở đây, event_name có thể là hoạt động cơ sở dữ liệu INSERT, DELETE, và UPDATE trên bảng table_name được đề cập. Bạn có thể tùy ý chỉ định FOR EACH ROW sau table name.

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 chỉ định của bảng.

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

3. Ví dụ của trigger trong SQLite

Giả sử chúng ta đã có bảng COMPANY có cấu trúc định nghĩa bảng như sau:

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

Và bảng AUDIT có cấu trúc định nghĩa bảng như sau:

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

Bây giờ chúng ta sẽ tạo một trigger trên bảng COMPANY 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;

Bây giờ, chúng ta hãy bắt đầu chèn một bản ghi vào bảng COMPANY, điều này sẽ tạo ra một bản ghi trong bảng AUDIT.  Câu lệnh để tạo một bản ghi trong bảng COMPANY như sau:

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

Điều này sẽ tạo một bản ghi trong bảng COMPANY như sau:

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

Đồng thời, 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 trong bảng COMPANY. Tương tự, chúng ta có thể tạo các trigger của mình trên các hoạt động UPDATE và DELETE tùy theo yêu cầu của mình.

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

4. Liệt kê các Trigger trong SQLite

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

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

Câu lệnh SQLite ở trên sẽ liệt kê chỉ một trigger như sau:

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

Nếu bạn muốn liệt kê các kích hoạt trên một bảng cụ thể, thì hãy sử dụng toán tử AND với tên bảng như sau:

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

Câu lệnh SQLite ở trên cũng sẽ liệt kê chỉ một trigger như sau:

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

5. Lệnh xóa trigger trong SQLite

Sau đây là lệnh DROP, có thể được sử dụng để xóa trigger hiện có.

sqlite> DROP TRIGGER trigger_name;