触发器(Trigger)
触发器(trigger)是数据库提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以查询其他表,而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根据客户当前的帐户状态,控制是否允许插入新订单。
触发器也可用于强制引用完整性,以便在多个表中添加、更新或删除行时,保留在这些表之间所定义的关系。然而,强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图,则可以在表之间创建关系以自动创建外键约束。 触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行。触发器(trigger):监视某种情况,并触发某种操作。触发器创建语法四要素:监视地点(table) 、监视事件(insert/update/delete) 、触发时间(after/before) 、触发事件(insert/update/delete)。语法:
create trigger<[after/before]> <[insert/update/delete]> on <表名> for each row #这句话在mysql是固定的begin sql语句;end; 表名>
首先我们来创建两张表:
#商品表create table g( id int primary key auto_increment, name varchar(20), num int);#订单表create table o( oid int primary key auto_increment, gid int, much int);insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);
如果我们在没使用触发器之前:假设我们现在卖了3个商品1,我们需要做两件事
- 往订单表插入一条记录:insert into o(gid,much) values(1,3);
- 更新商品表商品1的剩余数量:update g set num=num-3 where id=1;
现在,我们来创建一个触发器:需要先执行该语句:
delimiter $(意思是告诉mysql语句的结尾换成以$结束)create trigger tg1after insert on ofor each row beginupdate g set num=num-3 where id=1;end$
这时候我们只要执行:insert into o(gid,much) values(1,3)$。会发现商品1的数量变为7了,说明在我们插入一条订单的时候,触发器自动帮我们做了更新操作。
但现在会有一个问题,因为我们触发器里面num和id都是写死的。比如:我们往订单表再插入 一条记录:insert into o(gid,much)values(2,3),执行完后会发现商品1的数量变4了,而商品2的数量没变,这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。我们如何在触发器引用行的值,也就是说我们要得到我们新插入的订单记录中的gid或much的值。对于insert而言,新插入的行用new来表示,行中的每一列的值用new.列名来表示。所以现在我们可以这样来改我们的触发器
create trigger tg2after insert on ofor each row beginupdate g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)end$
我们先把第一个触发器删掉:drop trigger tg1$。再来测试一下,插入一条订单记录:insert into o(gid,much) values(2,3)$执行完发现商品2的数量变为7了,现在就对了。
现在还存在两种情况:
- 当用户撤销一个订单的时候,我们这边直接删除一个订单,我们是不是需要把对应的商品数量再加回去呢?
- 当用户修改一个订单的数量时,我们触发器修改怎么写?
分析一下第一种情况:监视地点:o、表监视事件:delete、触发时间:after、触发事件:update。对于delete而言:原本有一行,后来被删除,想引用被删除的这一行,用old来表示,old.列名可以引用被删除的行的值。那我们的触发器就该这样写:
create trigger tg3after delete on ofor each rowbeginupdate g set num = num + old.much where id = old.gid;(注意这边的变化)end$
再执行delete from o where oid = 2$,会发现商品2的数量又变为10了。
第二种情况:监视地点:o表、监视事件:update、触发时间:after、触发事件:update。对于update而言:被修改的行,修改前的数据,用old来表示,old.列名引用被修改之前行中的值;修改的后的数据,用new来表示,new.列名引用被修改之后行中的值。那我们的触发器就该这样写:
create trigger tg4after update on ofor each rowbeginupdate g set num = num+old.much-new.much where id = old/new.gid;end$
先把旧的数量恢复再减去新的数量就是修改后的数量了。我们来测试下:先把商品表和订单表的数据都清掉,易于测试。假设我们往商品表插入三个商品,数量都是10,买3个商品1:insert into o(gid,much) values(1,3)$ 这时候商品1的数量变为7;我们再修改插入的订单记录: update o set much = 5 where oid = 1$。我们变为买5个商品1,这时候再查询商品表就会发现商品1的数量只剩5了,说明我们的触发器发挥作用了。
after和before的区别:after是先完成数据的增删改,再触发,触发的语句晚于监视的增删改操作,无法影响前面的增删改动作;也就是说先插入订单记录,再更新商品的数量;before是先完成触发,再增删改,触发的语句先于监视的增删改,我们就有机会判断,修改即将发生的操作;
用一个典型案例来区分它们的区别,新建一个触发器:监视地点: 商品表o、监视事件:insert、触发时间:before、触发事件:update。案例:当新增一条订单记录时,判断订单的商品数量,如果数量大于10,就默认改为10
create trigger tg6before insert on ofor each rowbegin if new.much > 10 then set new.much = 10; end if; update g set num = num - new.much where id = new.gid;end$
建立的数据库一般都是 InnoDB 数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL会回滚事务,有:
- 如果BEFORE触发器执行失败,SQL无法正确执行
- SQL执行失败时,AFTER型触发器不会触发
- AFTER类型的触发器执行失败,SQL会回滚
存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。