定义:是用户定义在关系表上的一类由时间驱动的数据对象;也是一种保障数据完整性的方法
分类:
-
insert触发器
insert触发器代码里,可以引用一个名为NEW的虚拟表(它在内存或者缓存中),用来访问被插入的行,后面的例子我们来结合着理解下.
在BEFORE INSERT 触发器中,NEW 中的值可以被更新。
- AFTER,BEFORE
mysql> create trigger qiao_test.customers_insert_trigger #在qiao_test库创建一个触发器名为customers_insert_trigger -> AFTER INSERT #在customers中每执行一次insert操作后 -> on qiao_test.customers FOR EACH ROW -> set @str='one customer added!'; #定义变量的值. mysql> insert into qiao_test.customers -> values(0,'qiaoying','m','wanshoulu','zhongguo'); Query OK, 1 row affected (0.00 sec) mysql> select @str; +---------------------+ | @str | +---------------------+ | one customer added! | +---------------------+ mysql> drop trigger qiao_test.customers_insert_trigger; Query OK, 0 rows affected (0.00 sec) #删除该触发器
意思就是每次向该表插入一行数据时,执行完insert操作之后,将这个变量的值进行定义.
而for each row语法:个人理解为该表中的不管哪一行做(insert,delete,update)操作,都会出发该触发器,该语法不是必须的.
mysql> create trigger qiao_test.customers_insert_trigger -> AFTER INSERT -> on qiao_test.customers -> FOR EACH ROW -> set @str=new.cust_id; #每执行insert操作之后,将str变量的值设置为new表里的cust_id的值.new表就是一个虚表,它会将我们执行insert的行读取; mysql> insert into qiao_test.customers -> values(0,'yi','m','cuiwei','zhongguo'); Query OK, 1 row affected (0.01 sec) #我这里该字段设置了自增张. mysql> select * from qiao_test.customers where cust_name='yi'; +---------+-----------+----------+----------+-----------+ | cust_id | cust_name | cust_sex | cust_add | cust_city | +---------+-----------+----------+----------+-----------+ | 105 | yi | m | cuiwei | zhongguo | +---------+-----------+----------+----------+-----------+ 1 row in set (0.00 sec) mysql> select @str; +------+ | @str | +------+ | 105 | +------+ 1 row in set (0.00 sec)
-
delete触发器
在DELETE 触发器代码内,可引用一个名为OLD(不区分大小写)的虚拟表,来访问被删除
的行。
OLD 中的值全部是只读的,不能被更新。mysql> create trigger qiao_test.customers_delete_trigger -> AFTER DELETE -> on qiao_test.customers -> for each row -> set @str=old.cust_name; Query OK, 0 rows affected (0.02 sec) #创建delete触发器,每执行delete操作后,会将str变量的值设置为已经执行delete操作的cust_name字段的值. mysql> delete from qiao_test.customers -> where cust_name='yi'; Query OK, 1 row affected (0.00 sec) mysql> select @str; +------+ | @str | +------+ | yi | +------+ 1 row in set (0.00 sec)
-
update触发器
在UPDATE 触发器代码内,可引用一个名为OLD(不区分大小写)的虚拟表,来访问UPDATE
语句执行前的值,也可以引用一个名为NEW(不区分大小写)的虚拟表来访问更新后的值。update触发器相当于是执行了一个delete+insert.
mysql> create trigger qiao_test.customers_update_trigger -> BEFORE UPDATE -> on qiao_test.customers FOR EACH ROW -> set new.cust_add=old.cust_city; Query OK, 0 rows affected (0.01 sec) #创建before update触发器,一般使用before的场景:设置的触发表被更新时使用before;如这个例子中我们的处发表是customers,即将要更新的也是customers表中的cust_id这一个字段. mysql> update qiao_test.customers -> set cust_add='pingguoyuan' -> where cust_name='qi'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #这里使用update语句更改qi的cust_name字段的值为'pingguoyuan' mysql> select * from qiao_test.customers -> where cust_name='qi'; +---------+-----------+----------+----------+-----------+ | cust_id | cust_name | cust_sex | cust_add | cust_city | +---------+-----------+----------+----------+-----------+ | 103 | qi | f | hangzhou | hangzhou | +---------+-----------+----------+----------+-----------+ 1 row in set (0.00 sec) #整体下来我们可以把它理解:将old.cust_city的值赋给了new.cust_add