什么是触发器
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。——百度百科
创建触发器
CREATE TRIGGER <触发器名> <触发时机 BEFORE | AFTER>
<触发事件 INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row <触发器主体>
1 <触发器名>
触发器的名称,触发器在当前数据库中必须具有唯一性。如果要在某个特定数据库中创建,名称前面应该加上数据库的名称. 如 xxx_trigger_xxx 命名
2 <触发时机> BEFORE | AFTER
触发时机 BEFORE 和 AFTER,触发器被触发的时刻,表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件,则使用 BEFORE 选项;若希望在激活触发器的语句执行之后完成几个或更多的改变,则通常使用 AFTER 选项。
3 <触发事件> INSERT | UPDATE | DELETE
触发事件,用于指定激活触发器的语句的种类。
注意:三种触发器的执行时间如下。
触发器类型 | 触发器功能 | 激活触发器的语句 |
---|---|---|
INSERT触发器 | 将新行插入表时激活触发器。 | INSERT ,LOAD, DATA ,REPLACE |
DELETE触发器 | 从表中删除某一行数据时激活触发器。 | DELETE ,REPLACE |
UPDATE触发器 | 更改表中某一行数据时激活触发器。 | DELETE |
4 <表名>
与触发器相关联的表名,此表必须是永久性表,不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如,对于一张数据表,不能同时有两个 BEFORE UPDATE 触发器,但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器,或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。
5 <关键字 FOR EACH ROW>
一般是指行级触发,对于受触发事件影响的每一行都要激活触发器的动作。例如,使用 INSERT 语句向某个表中插入多行数据时,触发器会对每一行数据的插入都执行相应的触发器动作。
6 <触发器主体>
触发器动作主体,包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句,可使用 BEGIN…END 复合语句结构。
注意:每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER,因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。
在oracle触发器中,触发器分为行触发器和语句触发器
在oracle中,for each row如果不写,为语句触发器,无论update语句一次影响了多少行,都只执行一次触发事件。
在BEGIN…END语句中也可以定义变量,但是只能在BEGIN…END内部使用:
DECLARE var_name var_type [DEFAULT value] #定义变量,可指定默认值
SET var_name = value #给变量赋值
NEW和OLD的使用:
触发器类型 | NEW和OLD的使用: |
---|---|
INSERT触发器 | NEW表示将要或者已经新增的数据。 |
DELETE触发器 | OLD用来表示将要或者已经被删除的语句。NEW表示将要或者已经被修改的语句 |
UPDATE触发器 | OLD表示将要或者已经被删除的数据。 |
查看触发器
show triggers;
删除触发器
drop trigger if EXISTS <触发器名>
限制和注意事项
触发器会有以下两种限制:
1.触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。
2.不能再触发器中使用以显示或隐式方式开始或结束事务的语句,如START TRANS-ACTION,COMMIT或ROLLBACK。
注意事项:MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果是对非事务表进行操作,那么就无法回滚了,数据可能会出错。
总结
触发器是基于行触发的,所以删除、新增或者修改操作可能都会激活触发器,所以不要编写过于复杂的触发器,也不要增加过得的触发器,这样会对数据的插入、修改或者删除带来比较严重的影响,同时也会带来可移植性差的后果,所以在设计触发器的时候一定要有所考虑。
触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
数据库触发器有以下的作用:
1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
- 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。
- 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%。
2.审计。可以跟踪用户对数据库的操作。
-
审计用户操作数据库的语句。
-
把用户对数据库的更新写入审计表。
3.实现复杂的数据完整性规则
- 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。
- 提供可变的缺省值。
4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如,在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
- 在修改或删除时级联修改或删除其它表中的与之匹配的行。
- 在修改或删除时把其它表中的与之匹配的行设成NULL值。
- 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
- 触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。例如,可以在books.author_code 列上生成一个插入触发器,如果新值与auths.author_code列中的某值不匹配时,插入被回退。
5.同步实时地复制表中的数据。
6.自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。