mysql之触发器

1.为什么使用触发器

触发器其实是与表有关的数据库对象，满足定义条件的时候将触发，然后执行触发器里面定义好的语句。使用触发器可以保证确保数据库中的数据的完整性。

2.触发器的语法

 CREATE TRIGGER trigger_name trigger_time trigger_event ON tableName FOR EACH ROW trigger_stmt

解释：

trigger_time ：代表触发器的触发时间，如果为BEFORE 意思就是在检查约束前触发。如果为AFTER意思就是在检查约束后触发。

trigger_event ：代表触发器的触发事件。可以为INSERT 、UPDATE、DELETE。

3.触发器入门例子

准备工作：

创建订单表orders和商品表goods：

CREATE TABLE goods(
	gid int primary key,
	name varchar(20),
	num smallint
);
CREATE TABLE orders(
	oid int primary key,
	gid int,
	much smallint
);
insert into goods(gid,name,num) values(1,"dog",10),(2,"cat",20),(3,"pig",30);

先查看下是否创建了触发器：

show triggers;

创建触发器：

delimiter $$
create trigger t1 
after insert 
on orders 
for each row 
begin 
update goods set num=num-1 where gid=1;
end$$

操作：让订单表增加1个订单，让dog的数量减少1。

insert into orders values(111,1,1) $$

可以发现增加了订单表的同时，也减少了商品表的数量，dog为9了。

但是可以发现问题：在定义触发器时，将增加订单表和减少商品表的货物数量写死了。这样不利于灵活操作，则需要动态增加。

4.触发器实现动态增加

触发器的编写：在增加订单时，那么对应的gid的库存的货物的数量num减少new.much。

delimiter $
create trigger t1 
after insert 
on orders 
for each row 
begin 
update goods set num=num-new.much where gid=new.gid;
end$

操作：新增订单

insert into orders values(112,2,5)$

结果：

可以发现对应的货物表的数量num减少了5。可以发现采用了new的方式来定义新增的值。

5.触发器实现动态删除恢复

由于上例已经对表定义了触发器t1，在mysql中一个表只能对应一个触发器。所以要先删除已定义的触发器。

drop trigger t1$

然后创建删除的触发器：在我们删除订单表的时候，那么说明还没有发货，那么库存数量应该还原。

delimiter $
create trigger t1 
after delete 
on orders 
for each row 
begin 
update goods set num=num+old.much where gid=old.gid;
end$

操作：删除订单gid=2和much=5的订单。

delete from orders where gid=2 and much=5$

结果：

可以发现订单oid=112被删除了，同时再删除之后货物的数量num增加了5。可以发现采用old的方式来恢复删除的值。

6.触发器常用的命令

删除触发器：

drop trigger 触发器名称

查看触发器：

show triggers;

触发器逻辑操作：

mysql> delimiter //

mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account

    -> FOR EACH ROW

    -> BEGIN

    ->     IF NEW.amount < 0 THEN

    ->         SET NEW.amount = 0;

    ->     ELSEIF NEW.amount > 100 THEN

    ->         SET NEW.amount = 100;

    ->     END IF;

    -> END//

7.mysql中触发程序出错处理方式

（1）如果before触发程序失败，那么不执行相应行上的操作。

（2）仅当BEFORE触发程序（如果有的话）和行操作均已成功执行，才执行AFTER触发程序。

（3）如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。

注意：对于当前的事务表而言，如果触发程序失败，那么该语句执行的所有操作将会回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。譬如myISAM引擎不支持事务，那么也别指望该类型的表会在触发程序出错时会回滚等。