触发器是一种与表操作有关的数据库对象。当有触发器的数据表上出现指定事件时,将调用该触发器对象,即表上的操作事件触发表上的触发器执行。能决定触发器执行某个操作的事件有:(insert)插入语句、(update)更新语句和(delete)删除语句。使用触发器可以保证某些操作之间的一致性。
MySQL创建一个执行语句的触发器:
![](https://upload-images.jianshu.io/upload_images/3623363-1597a1b7eae749dd.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
trigger_name:触发器名称,可以自己定义;
trigger_time:触发时机,有两个取值BEFORE|AFTER,指定触发器执行的时间。
trigger_event:触发事件,取值包括INSERT UPDATE和DELETE。
tbl_name:建立触发器的数据表的名称,即在哪张表上建立触发器。
FOR EACH ROW:表示任何一条满足触发条件的记录上的操作都会触发触发器。
trigger_stmt:触发器程序体,指触发器被触发后执行的程序,可以是一条SQL语句,也可以是BEGIN和END包含的多条语句。
需要注意的是不能在同一张表上建立2种同类型的触发器,一张表最多创建6个触发器(6种类型):即BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
触发事件:MySQL除了对insert update delete基本操作进行定义外,还定义了load data和replace语句,这两种语句也能触发以上6种类型的触发器的触发。
LOAD DATA 语句将一个文件装入到一个数据表中,相当与是一系列的 INSERT 操作。
replace和insert语句也很像,只是在表中有 primary key 或 unique 索引时,如果插入的数据和原来 primary key 或 unique 索引一致时,会先删除原来的数据,然后增加一条新数据,也就是说,一条 REPLACE 语句有时候等价于一条。
INSERT触发器:插入某一行时激活触发器,可能通过 INSERT、LOAD DATA、REPLACE 语句触发;
UPDATE触发器:更改某一行时激活触发器,可能通过 UPDATE 语句触发;
DELETE触发器:删除某一行时激活触发器,可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 语句
语句格式:BEGIN statement...list END
statement_list 代表一个或多个语句的列表,列表内的每条语句都必须用分号(;)来结尾。
触发器示例:
使用Navicat 数据库管理工具管理的数据库,在某张数据表上建立触发器,首先找到要建立触发器的表,选择打开设计该表会看下如下图,然后点击上方Triggers(触发器),点击左下方的+,创建触发器,输入触发器的名称(Name),触发时机(Fires),触发操作(如下所示有insert update delete三个选项)。最后在statement中写入触发器程序体。点击Save保存,一个触发器就创建好了。
![](https://upload-images.jianshu.io/upload_images/3623363-d6cfd394c2434a5b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
然后对创建触发器的表执行一条插入语句,触发此触发器,触发器会执行程序体,就是statement中的SQL语句。
![](https://upload-images.jianshu.io/upload_images/3623363-88fccd41be4f3550.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
![](https://upload-images.jianshu.io/upload_images/3623363-7f56a9721b81c6c7.jpeg?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
NEW 与 OLD
上述示例中使用了NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。具体:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用。需注意:触发器不可以循环调用。
查看触发器
![](https://upload-images.jianshu.io/upload_images/3623363-908bcafaea42f578.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
删除触发器
drop trigger 触发器名。
触发器名参数如果指指定名称会在当前数据库下查找该触发器,如果找到就删除。如果指定数据库,数据库系统会到指定的数据库下去查找触发器。
如果不再需要某触发器,一定要将该触发器删除。
![](https://upload-images.jianshu.io/upload_images/3623363-bc3725b67989d6b2.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:
①如果 BEFORE 触发器执行失败,SQL 无法正确执行。
②SQL 执行失败时,AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败,SQL 会回滚。