SQLITE TRIGGERS【Sqlite触发器】

原创
2019/04/04 15:23
阅读数 40

Reference: 

https://sqlite.org/lang_createtrigger.html

http://www.runoob.com/sqlite/sqlite-trigger.html

触发器

触发器是,当特定的数据库事件被触发时,被自动执行的数据库操作。

可以指定触发器在发生特定数据库表的DELETE,INSERT或UPDATE时触发,也可以在表的一个或多个指定列上发生UPDATE时触发。

截止目前的版本(发文时,官网最新的版本是3.27.2),SQLite仅支持FOR EACH ROW触发器,而不支持FOR EACH STATEMENT触发器。因此,FOR EACH ROW声明是可选的。 FOR EACH ROW意味着当数据库INSERT,UPDATE或DELETE被执行时,每个受影响的行都会导致触发器被触发,从而执行触发器中指定的SQL语句。当然,是否每个受影响的行会导致触发器中的代码被执行,还要取决于触发器的WHEN子句。

WHEN子句和触发器操作都可以使用“NEW.column-name”和“OLD.column-name”形式的引用访问要插入,删除或更新的行的元素,其中column-name是与触发器关联的表中的列的名称。 OLD和NEW引用只能用于与它们相关的事件的触发器,如下所示:

INSERT      NEW引用有效 

UPDATE      NEW和OLD引用有效 

DELETE      OLD引用有效 

如果提供了WHEN子句,则仅在WHEN子句为true时才执行指定的SQL语句。如果未提供WHEN子句,则每次触发器触发时都会执行SQL语句。

BEFORE或AFTER关键字确定何时相对于相关行的插入,修改或删除执行触发操作。

可以将ON CONFLICT子句指定为触发器主体内的UPDATE或INSERT操作的一部分。但是,如果将ON CONFLICT子句指定为导致触发器触发的语句的一部分,则使用外部语句的冲突处理策略。

当删除与它们关联的表(表名表)时,会自动删除触发器。但是,如果触发器操作引用其他表,则在删除或修改其他表时不会删除或修改触发器。

使用DROP TRIGGER语句删除触发器。

创建和删除触发器的语法图示如下:

Create Triggers

syntax diagram create-trigger-stmt

Drop Triggers:

syntax diagram drop-trigger-stmt

触发器中的UPDATE,DELETE和INSERT语句的语法限制

触发器中的UPDATE,DELETE和INSERT语句不支持UPDATE,DELETE和INSERT语句的完整语法。以下限制适用:

要在UPDATE,DELETE或INSERT语句中修改的表的名称必须是非限定的表名。换句话说,在指定表时,必须只使用“tablename”而不是“database.tablename”。

对于非TEMP触发器,要修改或查询的表必须与触发器所连接的表或视图位于同一数据库中。 TEMP触发器不受相同数据库规则的约束。允许TEMP触发器查询或修改任何ATTACH-ed数据库中的任何表。

不支持INSERT语句的“INSERT INTO表DEFAULT VALUES”形式。

UPDATE和DELETE语句不支持INDEXED BY和NOT INDEXED子句。

不支持UPDATE和DELETE语句的ORDER BY和LIMIT子句。在任何上下文中,UPDATE或DELETE通常不支持ORDER BY和LIMIT,但可以使用SQLITE_ENABLE_UPDATE_DELETE_LIMIT编译时选项为顶级语句启用ORDER BY和LIMIT。但是,该编译时选项仅适用于顶级UPDATE和DELETE语句,而不适用于触发器中的UPDATE和DELETE语句。

触发器内的语句不支持公用表表达式。

INSTEAD OF触发器

通过在CREATE TRIGGER语句中指定INSTEAD OF,可以在视图和普通表上创建触发器。 如果在视图上定义了一个或多个ON INSERT,ON DELETE或ON UPDATE触发器,则分别对视图执行INSERT,DELETE或UPDATE语句不会发生错误。 相反,在视图上执行INSERT,DELETE或UPDATE会导致关联的触发器触发。 但是不会修改视图下的真实表数据(除非通过触发器程序明确地表示,才有可能)。 请注意,sqlite3_changes()和sqlite3_total_changes()接口不计算INSTEAD OF触发器触发,但count_changes pragma计算INSTEAD OF触发器触发。

关于使用BEFORE触发器的注意事项

如果BEFORE UPDATE或BEFORE DELETE触发器修改或删除了已更新或删除的行,则后续更新或删除操作的结果是未定义的。 此外,如果BEFORE触发器修改或删除了一行,那么未定义是否会在这些行上运行的AFTER触发器实际上会运行。

在BEFORE INSERT触发器中未定义NEW.rowid的值,其中rowid未显式设置为整数。

由于上述行为,鼓励程序员更喜欢AFTER触发器而不是BEFORE触发器。

RAISE() 函数

可以在触发器程序中使用特殊的SQL函数RAISE(),语法如下

raise_function()

syntax diagram raise-function

当在触发程序执行期间调用RAISE(ROLLBACK,...),RAISE(ABORT,...)或RAISE(FAIL,...)之一时,执行指定的ON CONFLICT处理,当前查询终止。 将SQLITE_CONSTRAINT的错误代码与指定的错误消息一起返回给应用程序。

当调用RAISE(IGNORE)时,将放弃当前触发器程序的其余部分,导致触发器程序执行的语句以及将执行的任何后续触发器程序。 不回滚数据库更改。 如果导致触发器程序执行的语句本身是触发器程序的一部分,那么该触发器程序将在下一步开始时继续执行。

非TEMP表上的TEMP触发器

触发器通常存在于与CREATE TRIGGER语句中“ON”关键字之后命名的表相同的数据库中。 除此之外,可以在另一个数据库的表上创建TEMP TRIGGER。 只有在定义触发器的应用程序对目标表进行更改时,才会触发此类触发器。 修改数据库的其他应用程序将无法看到TEMP触发器,因此无法运行触发器。

在非TEMP表上定义TEMP触发器时,指定包含非TEMP表的数据库很重要。 例如,在以下语句中,重要的是要说“main.tab1”而不是“tab1”: CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN .. 如果发生任何架构更改,则无法在目标表上指定架构名称可能导致TEMP触发器重新连接到另一个数据库中具有相同名称的表。

举例

OS: Ubuntu18.04

安装sqlite3:sudo apt install sqlite3

创建数据库test.db并进入sqlite命令提示符:sqlite3 test.db

执行以下命令打开相关选项(非必要):

sqlite>.header on
sqlite>.mode column
sqlite>.timer on

执行以下命令创建表:

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

CREATE TABLE AUDIT(
ID INT NOT NULL,
FLAG TINYINT NOT NULL,
VALUE TEXT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);

向company表中插入数据:

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

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

 为company创建触发器:

sqlite> CREATE TRIGGER AUDIT_INS AFTER INSERT ON COMPANY
   ...> BEGIN
   ...> INSERT INTO AUDIT VALUES(NEW.ID, 0, NEW.NAME + '_' + NEW.SALARY, DATETIME('NOW'));
   ...> END;
Run Time: real 0.064 user 0.000000 sys 0.000000
sqlite> CREATE TRIGGER AUDIT_DEL AFTER DELETE ON COMPANY
   ...> BEGIN
   ...> INSERT INTO AUDIT VALUES(OLD.ID, 2, OLD.NAME + OLD.AGE + OLD.SALARY + OLD.ADDRESS, DATETIME('NOW'));
   ...> END;
Run Time: real 0.082 user 0.000000 sys 0.000000
sqlite> CREATE TRIGGER AUDIT_UPD AFTER UPDATE ON COMPANY
   ...> BEGIN
   ...> INSERT INTO AUDIT VALUES(NEW.ID, 1, OLD.ID + '-' + OLD.NAME + OLD.AGE + OLD.SALARY + OLD.ADDRESS, DATETIME('NOW'));
   ...> END;

此时执行

.schema company

可以查看表company的触发器情况。

向company表中插入一条数据,然后查看audit表中的记录:

sqlite> INSERT INTO COMPANY VALUES(10, 'Kerk', 26, 'Canada', 110000.0);
Run Time: real 0.132 user 0.000000 sys 0.015625
sqlite> SELECT * FROM AUDIT;
ID          FLAG        VALUE       ENTRY_DATE
----------  ----------  ----------  -------------------
10          0           110000.0    2019-04-04 06:13:44
Run Time: real 0.005 user 0.000000 sys 0.000000
sqlite>

从company表删除2条记录:

sqlite> DELETE FROM COMPANY WHERE ID IN (2,5);
Run Time: real 0.095 user 0.000000 sys 0.000000
sqlite> SELECT * FROM AUDIT;
ID          FLAG        VALUE       ENTRY_DATE
----------  ----------  ----------  -------------------
10          0           110000.0    2019-04-04 06:13:44
2           2           150025.0    2019-04-04 06:16:29
5           2           850027.0    2019-04-04 06:16:29
Run Time: real 0.007 user 0.000000 sys 0.000000
sqlite>

更新一条记录:

sqlite> UPDATE COMPANY SET SALARY = 333333 WHERE ID = 3;
Run Time: real 0.138 user 0.000000 sys 0.015625
sqlite> SELECT * FROM AUDIT;
ID          FLAG        VALUE       ENTRY_DATE
----------  ----------  ----------  -------------------
10          0           110000.0    2019-04-04 06:13:44
2           2           150025.0    2019-04-04 06:16:29
5           2           850027.0    2019-04-04 06:16:29
3           1           200026.0    2019-04-04 06:18:38
Run Time: real 0.008 user 0.000000 sys 0.000000
sqlite>

INSTEAD OF触发器:

创建视图:

sqlite> CREATE VIEW name_address AS SELECT ID, NAME, ADDRESS FROM COMPANY;
Run Time: real 0.165 user 0.000000 sys 0.000000

此时先尝试一下,直接对视图进行更新,是否会更新源表的数据:

sqlite> update name_address set address = 'Florida' where id = 2;
Run Time: real 0.001 user 0.000000 sys 0.000000
Error: cannot modify name_address because it is a view
sqlite>

然后sqlite给我们返回了一个错误:无法修改name_address,因为它是一个视图。

为表company创建一个触发器,然后重新执行上述更新语句:

sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  200000.0
3           Teddy       23          Norway      333333.0
4           Marks       25          Richmond    650000.0
6           Kim         22          South Dako  450000.0
7           James       28          Washington  180000.0
10          Kerk        26          Canada      110000.0
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite> CREATE TRIGGER name_addr_changes INSTEAD OF UPDATE OF NAME, ADDRESS ON name_address
   ...> BEGIN
   ...> UPDATE COMPANY SET NAME = NEW.NAME, ADDRESS = NEW.ADDRESS WHERE ID = NEW.ID;
   ...> END;
Run Time: real 0.092 user 0.000000 sys 0.000000
sqlite> update name_address set address = 'Florida' where id = 2;
Run Time: real 0.001 user 0.000000 sys 0.000000
sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  200000.0
3           Teddy       23          Norway      333333.0
4           Marks       25          Richmond    650000.0
6           Kim         22          South Dako  450000.0
7           James       28          Washington  180000.0
10          Kerk        26          Canada      110000.0
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite> update name_address set address = 'Florida' where id = 3;
Run Time: real 0.094 user 0.000000 sys 0.031250
sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  200000.0
3           Teddy       23          Florida     333333.0
4           Marks       25          Richmond    650000.0
6           Kim         22          South Dako  450000.0
7           James       28          Washington  180000.0
10          Kerk        26          Canada      110000.0
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite>

可以看到没有任何错误,当在源表中找到对应的记录时,会将源表的数据更新。

如果触发器中的语句不对源表进行操作,会发生什么呢?

sqlite> CREATE TRIGGER name_addr_audit INSTEAD OF UPDATE OF NAME, ADDRESS ON name_address
   ...> BEGIN
   ...> INSERT INTO AUDIT VALUES(NEW.ID, 1, OLD.ID + '-' + OLD.NAME + '-' + OLD.ADDRESS, DATETIME('NOW'));
   ...> END;
Run Time: real 0.082 user 0.000000 sys 0.000000
sqlite> DROP TRIGGER name_addr_changes;
Run Time: real 0.101 user 0.000000 sys 0.015625
sqlite> update name_address set address = 'Alabama' where id = 3;
Run Time: real 0.116 user 0.000000 sys 0.000000
sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  200000.0
3           Teddy       23          Florida     333333.0
4           Marks       25          Richmond    650000.0
6           Kim         22          South Dako  450000.0
7           James       28          Washington  180000.0
10          Kerk        26          Canada      110000.0
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite> select * from audit;
ID          FLAG        VALUE       ENTRY_DATE
----------  ----------  ----------  -------------------
10          0           110000.0    2019-04-04 06:13:44
2           2           150025.0    2019-04-04 06:16:29
5           2           850027.0    2019-04-04 06:16:29
3           1           200026.0    2019-04-04 06:18:38
2           0           150000.0    2019-04-04 06:38:33
5           0           850000.0    2019-04-04 06:38:33
2           2           150025.0    2019-04-04 06:44:40
5           2           850027.0    2019-04-04 06:44:40
3           1           333359.0    2019-04-04 06:53:32
3           1           3           2019-04-04 06:58:46
Run Time: real 0.015 user 0.000000 sys 0.000000
sqlite>

此时,对视图的更新操作仍然可以成功,只是不会再更新源表数据了。

备注:由于创建触发器使用了“+”号,导致字符串按照数值计算了,把“+”号改为”||“,即可实现字符串拼接:

sqlite> drop trigger name_addr_audit;
Run Time: real 0.096 user 0.000000 sys 0.000000
sqlite> CREATE TRIGGER name_addr_audit INSTEAD OF UPDATE OF NAME, ADDRESS ON name_address
   ...> BEGIN
   ...> INSERT INTO AUDIT VALUES(NEW.ID, 1, OLD.ID || '-' || OLD.NAME || '-' || OLD.ADDRESS, DATETIME('NOW'));
   ...> END;
Run Time: real 0.103 user 0.000000 sys 0.015625
sqlite> update name_address set address = 'Alabama' where id = 3;
Run Time: real 0.107 user 0.000000 sys 0.015625
sqlite> select * from audit;
ID          FLAG        VALUE       ENTRY_DATE
----------  ----------  ----------  -------------------
10          0           110000.0    2019-04-04 06:13:44
2           2           150025.0    2019-04-04 06:16:29
5           2           850027.0    2019-04-04 06:16:29
3           1           200026.0    2019-04-04 06:18:38
2           0           150000.0    2019-04-04 06:38:33
5           0           850000.0    2019-04-04 06:38:33
2           2           150025.0    2019-04-04 06:44:40
5           2           850027.0    2019-04-04 06:44:40
3           1           333359.0    2019-04-04 06:53:32
3           1           3           2019-04-04 06:58:46
3           1           3-Teddy-Fl  2019-04-04 07:17:04
Run Time: real 0.016 user 0.000000 sys 0.000000
sqlite>

另外,表和触发器是各自独立的,这一点可以从把带有触发器的表删除不会发生错误,或把创建到某表的触发器删除也不会发生错误上看出。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部