SQLite - 觸發器

SQLite 觸發器是資料庫回撥函式，當指定的資料庫事件發生時，會自動執行/呼叫它們。以下是關於 SQLite 觸發器的要點：

可以指定 SQLite 觸發器在特定資料庫表發生 DELETE、INSERT 或 UPDATE 操作時觸發，或者在表的一個或多個指定列上發生 UPDATE 操作時觸發。
目前，SQLite 只支援 FOR EACH ROW 觸發器，不支援 FOR EACH STATEMENT 觸發器。因此，顯式指定 FOR EACH ROW 是可選的。
WHEN 子句和觸發器操作都可以使用 NEW.column-name 和 OLD.column-name 形式的引用來訪問正在插入、刪除或更新的行中的元素，其中 column-name 是與觸發器關聯的表的列名。
如果提供了 WHEN 子句，則只有當 WHEN 子句為真時，才會執行指定的 SQL 語句。如果沒有提供 WHEN 子句，則會對所有行執行 SQL 語句。
BEFORE 或 AFTER 關鍵字決定觸發器操作相對於關聯行的插入、修改或刪除何時執行。
當與之關聯的表被刪除時，觸發器會自動刪除。
要修改的表必須與附加觸發器的表或檢視位於同一個資料庫中，並且必須只使用 tablename 而不是 database.tablename。
可以在觸發器程式中使用特殊的 SQL 函式 RAISE() 來引發異常。

語法

以下是建立觸發器的基本語法。

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

這裡，event_name 可以是提到的表 table_name 上的INSERT、DELETE 和UPDATE 資料庫操作。您可以在表名之後選擇性地指定 FOR EACH ROW。

以下是針對表的一個或多個指定列上的 UPDATE 操作建立觸發器的語法。

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

示例

讓我們考慮一個案例，我們希望為 COMPANY 表中插入的每個記錄都保留審計跟蹤，我們新建立該表如下（如果您已經有了 COMPANY 表，請將其刪除）。

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

為了保留審計跟蹤，我們將建立一個名為 AUDIT 的新表，每當 COMPANY 表中為新記錄建立條目時，日誌訊息將被插入到該表中。

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

這裡，ID 是 AUDIT 記錄的 ID，EMP_ID 是將來自 COMPANY 表的 ID，DATE 將保留在 COMPANY 表中建立記錄的時間戳。現在讓我們在 COMPANY 表上建立觸發器，如下所示：

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

現在，我們將開始實際工作，讓我們開始在 COMPANY 表中插入記錄，這應該會導致在 AUDIT 表中建立一個審計日誌記錄。在 COMPANY 表中建立一個記錄，如下所示：

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

這將在 COMPANY 表中建立一個記錄，如下所示：

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

同時，將在 AUDIT 表中建立一個記錄。此記錄是我們之前在 COMPANY 表的 INSERT 操作上建立的觸發器產生的結果。類似地，您可以根據您的需求在 UPDATE 和 DELETE 操作上建立觸發器。

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

列出觸發器

您可以從sqlite_master 表中列出所有觸發器，如下所示：

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

上述 SQLite 語句將僅列出以下一個條目：

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

如果要列出特定表上的觸發器，請使用 AND 子句和表名，如下所示：

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

上述 SQLite 語句也將僅列出以下一個條目：

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

刪除觸發器

以下是 DROP 命令，可用於刪除現有觸發器。

sqlite> DROP TRIGGER trigger_name;

列印頁面