SQLite 触发器

SQLite 触发器 是数据库回调函数，当指定的数据库事件发生时会自动执行/调用。以下是关于SQLite触发器的重要要点：

• SQLite触发器可以在特定数据库表的删除、插入或更新和在表的一个或多个指定列上的更新发生时触发。

• 目前，SQLite仅支持FOR EACH ROW触发器，不支持FOR EACH STATEMENT触发器。因此，显式指定FOR EACH ROW是可选的。

• WHEN子句和触发器操作可以使用以下形式的引用访问要插入、删除或更新的行元素： NEW.column-name 和 OLD.column-name ，其中column-name是触发器关联的表的列名。

• 如果提供WHEN子句，指定的SQL语句仅对WHEN子句为真的行执行。如果没有提供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是来自公司表的ID，DATE将保存记录在公司表中创建的时间戳。现在让我们在公司表上创建触发器，如下所示 –

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

现在，我们将开始实际工作，让我们开始在公司表中插入记录，这应该会在审计表中创建一个审计日志记录。在公司表中创建一条记录，如下所示：

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语句将只列出一条记录，如下所示−

这是一个HTML段落

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;