MySQL(十一)之触发器
上一篇介绍的是比较简单的视图,其实用起来是相对比较简单的,以后有什么更多的关于视图的用法,到时候在自己补充。接下来让我们一起了解一下触发器的使用!
一、触发器概述
1.1、什么是触发器
触发器(Trigger):监视某种情况,并触发某种操作。在MySQL Server里面也就是对某一个表的一定的操作,触发某种条件(Insert,Update,Delete 等),从而自动执行的一段程序。
注意:你必须拥有相当大的权限才能创建触发器(CREATE TRIGGER),如果你已经是Root用户,那么就足够了。这跟SQL的标准有所不同。
1.2、触发器作用
那么为什么要使用数据库对象触发器呢?在具体开发项目时,经常会遇到如下实例:
1)在学生表中拥有字段学生姓名,字段学生总数,每当添加一条学生信息时,学生的总数就必须同时更改。
2)在学生表中还会有学生姓名的缩写,学生住址等字段,添加学生信息时,往往需要检查电话、邮箱等格式是否正确。
上面的例子使用触发器完成时具有这样的特点,需要在表发生改变时,自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作,其他SQL语句则不会激活触发器。
1.3、触发器四要素
监视地点:table
监听事件:insert/update/delete
触发时间:after/before
触发事件:insert/update/delete
二、触发器用法
2.1、触发器语法
CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。
简单的写就是这样:
create trigger tg_name after/before insert/update/delete on table for each row ####这句话在MySQL中是固定的 begin sql语句; end$
分析:由于在以上代码段中的“sql语句;”是以分号结尾,所以需要将MySQL中的结尾标志换成“$”,更换MySQL命令结束标志的命令:delimiter $;
以上触发器语法中的各个段的颜色与四要素对应查看理解。
2.2、创建触发器
1)创建两张表
create table tb_goods(id int primary key auto_increment,name varchar(20),num int); create table tb_orders(id int primary key auto_increment,good_id int,much int);
2)在商品表中插入数据
insert into tb_goods(name,num)values('商品1',10),('商品2',10),('商品3',10);
3)假如我们卖了3个商品1
没有触发器:
往订单表中插入一条记录:insert into tb_orders(good_id,much) values(1,3);
更新商品表中商品1的剩余数量:update tb_goods set num=num-3 where id=1;
创建触发器:
create trigger tg_1 after insert on tb_orders
for each row
begin
update tb_goods set num=num-3;
end$
这个时候如果执行insert into tb_orders(good_id,much) values(1,3);会发现商品的数量变为7了,说明在插入一条订单的时候,触发器自动做了更新操作。
2.3、触发器对值得引用
上述触发器有一个问题,因为在触发器中写死了num和id,所以不管买哪个商品,最终更新的都是商品1的数量。这个时候,需要将触发器中的值变为动态获取。
对于insert来说,新插入的行用new来表示,行中的每一列的值用“new.列名”来表示:
新建能动态获取值的触发器:
create trigger tg_2 after insert on tb_orders for each row begin update tb_goods set num=num-new.much where id=new.id; end$
删除第一个触发器:drop trigger tg_1;
插入一条订单记录:insert into tb_orders(good_id,much) values(2,3)$
执行完发现商品的数量变为7了,这样子就是合适的。
三、触发器实例
1)创建表tab1
DROP TABLE IF EXISTS tab1; CREATE TABLE tab1( tab1_id varchar(11) );
2)创建表tab2
DROP TABLE IF EXISTS tab2; CREATE TABLE tab2( tab2_id varchar(11) );
3)创建触发器:t_afterinsert_on_tab1
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW BEGIN insert into tab2(tab2_id) values(new.tab1_id); END;
4)想tab1插入数据
INSERT INTO tab1(tab1_id) values('0001');
5)查看变化
SELECT * FROM tab1;
SELECT * FROM tab2;