触发器

触发器（Trigger）

　　触发器（trigger）是数据库提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。
　　触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。
　　触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。　

　　触发器(trigger)：监视某种情况，并触发某种操作。触发器创建语法四要素：监视地点(table) 、监视事件(insert/update/delete) 、触发时间(after/before) 、触发事件(insert/update/delete)。语法：

create trigger <triggerName>
<[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，我们需要做两件事

1. 往订单表插入一条记录:insert into o(gid,much) values(1,3);
2. 更新商品表商品1的剩余数量:update g set num=num-3 where id=1;

　　现在，我们来创建一个触发器：需要先执行该语句：

delimiter $(意思是告诉mysql语句的结尾换成以$结束)
create trigger tg1
after insert on o
for each row 
begin
update 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 tg2
after insert on o
for each row 
begin
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
end$

　　我们先把第一个触发器删掉：drop trigger tg1$。再来测试一下，插入一条订单记录：insert into o(gid,much) values(2,3)$执行完发现商品2的数量变为7了，现在就对了。

　　现在还存在两种情况：

1. 当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？
2. 当用户修改一个订单的数量时，我们触发器修改怎么写?

　　分析一下第一种情况：监视地点：o、表监视事件：delete、触发时间：after、触发事件：update。对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。那我们的触发器就该这样写：

create trigger tg3
after delete on o
for each row
begin
update 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 tg4
after update on o
for each row
begin
update 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 tg6
before insert on o
for each row
begin
　　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 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。