SQL触发器trigger, since 2021-12-20

2021-12-20 本文已影响0人 Mc杰夫

(2021.12.23 Thur)
触发器有时也称作事件-条件-动作规则(event-condition-action rule)，或ECA。触发器有如下特点

仅当数据库程序员生命的事件发生时，触发器被激活。允许的事件种类是对某个特定关系的INSERT, UPDATE和DELETE。很多SQL系统中允许的另一种事件是事务的结束。
当触发器被事件激活时，trigger测试触发的条件(condition)。如果条件不成立，则响应该事件的触发器不做任何事。
如果trigger声明的条件满足，则与该触发器相连的动作(action)有DBMS执行。动作可以是以某种方式修改事件的结果，甚至可以是撤销事件所在的事务。事实上，动作可以使任何数据库操作的序列，包括与触发事件毫无关联的操作。

触发器的特征

条件检察和动作可以在触发事件执行之前的数据库的状态(state of the database)(即当前所有关系的实例)上或在出发动作被执行后的状态上执行。BEFORE/AFTER
条件和动作可以引用元组的旧值和/或触发动作被执行后的状态上执行。NEW/OLD
更新事件可以被局限到某个特定的属性或某一些属性
可选择动作执行的方式：
a) 一次只对一个更新元组(row-level trigger，行级触发器) 或
b) 一次针对在数据库操作中被改变的所有元组(statement-level trigger，语句级触发器，记住一个SQL更新语句可以影响许多元组)。

触发器的格式

CREATE TRIGGER <trigger_name>
<timing> <action> [OF <field1>] ON <table1>
[REFERENCING
OLD ROW AS <name_old>
NEW ROW AS <name_new>
]
FOR EACH <method>
BEGIN
<sql_scripts>
END

记得在MySQL中还要在该格式的前后加上DELIMITER\\和DELIMITER;。

<trigger_name>: 触发器的命名
<timing>: 时机，只有两个选项BEFORE和AFTER，代表着触发动作之前的前/后对特定表格做修改
<action>: 触发的条件动作，即当某个动作发生时，触发后面的操作。有三个动作，INSERT，UPDATE和DELETE
<field1>: 可选。代表着条件动作中需要修改的列
<table1>: 触发条件动作的表格
<name_old/name_new>: 可选。该REFERENCING子句允许触发器的动作和动作引用正被修改的元组，在更新的情况下，盖子局允许给改变之前和之后的元组命名。如果不使用该子句，则只需要用NEW和OLD代表修改前后的元组即可。
<method>: 有ROW和STATEMENT两种可选，告诉触发器支队每个修改的行执行一次，还是对由SQL语句做的所有修改执行一次的子句。默认是FOR EACH STATEMENT
<sql_scripts>: 被触发器触发的动作

例1，该案例中每次对MovieExec表中的netWorth字段做按行的UPDATE之后，并且修改前的数据大于修改后的数据的情况下，设定MoveExec中的netWorth保留为原数据。

DELIMITER &&
CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
          OLD ROW AS OldTuple
          NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
           UPDATE MovieExec
           SET netWorth = OldTuple.netWorth
           WHERE cert# = NewTuple.cert#;
DELIMITER;

new/old

(2021.12.24 Fri)
每行数据在操作前后都有一个状态，trigger将没有操作之前的状态保存到old中，将操作之后的保存到new关键字中。调用格式是

new/old.<field_name>

INSERT关键字：只有new没有old，
UPDATE关键字：有new也有old，old表示更新前的数据，new表示更新后的数据
DELETE关键字：只有old没有new，old表示被删除前的数据

这里的new/old代表的是触发条件的数据。比如，对关系A的修改加入或删除会触发关系B变化，这里的A可以被new/old指代。在触发的动作里，可通过new/old.<field_name>引用触发条件关系的数据。

行级、语句级触发器

一旦有合适类型的语句被执行，语句级触发器就被执行，而不问它实际上会影响多少个元组。例如，如果用SQL更新语句更新整个表，语句级的修改触发器将只被执行一次，而元组级触发器将对要修改的元组一次一个的执行。

下面这个例子，在更新MovieExec的元组过程中，平均净资产可以暂时的低于500000，当所有变更结束时，其净资产值将超过500000。约束要做的是，若语句执行结束后，净资产值仍然低于500000，则整个一组更新操作被拒绝。

CREATE TRIGGER AvgNetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING 
         OLD TABLE AS OldStuff
         NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec))
BEGIN
         DELETE FROM MovieExec
         WHERE (name, address, cert#, netWorth) IN NewStuff;
         INSERT INTO MovieExec
                 (SELECT * FROM OldStuff);
END;

命令

查看触发器

SHOW TRIGGERS;
SHOW TRIGGERS/G;

查看触发器的创建语句

SHOW CREATE TRIGGER <trigger_name>;
SHOW CREATE TRIGGER <trigger_name>/G;

删除触发器

DROP TRIGGER <trigger_name>;

限制

触发器不能使用CALL 语句来将数据返回给客户端或使用动态SQL的存储过程。但允许存储过程通过OUT或INOUT 参数将数据返回到触发器
触发不能使用事务相关的语句，如 START TRANSACTION，COMMIT或ROLLBACK。因为触发器对update,delete,insert等事件做了处理，并且是按照before，SQL语句，after的顺序来执行的，一旦某一步出错，就会回滚数据。如果在触发器中使用事务，就会产生矛盾。

Reference

1 Jeffery U.等著，岳丽华等译，数据库系统基础教程，机械工业出版社