触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

DELETE；

INSERT；

UPDATE；

    使用触发器，需要MySQL5或之后的版本支持。

一、触发器基本操作

1、创建触发器

创建触发器时，需要给出4条信息：

  唯一的触发器名；(虽然MySQL5允许不同的表上的触发器名称相同，但一般最好不要这么做。)

  触发器关联的表；

  触发器响应的事件；

  触发器何时执行；

  语法结构：

create trigger trigger_name (BEFORE|AFTER) (delete|update|insert) on table_name

for each row  -- 这句话在mysql是固定的，通知触发器每隔一行执行一次动作，而不是对整个表

BEGIN

要触发的sql语句;

END;

CREATE TABLE t_goods(id int,goods_name varchar(20),quantity int,add_date date);

delimiter |

create triggert_trig before insert on t_goods for each row

BEGIN

set NEW.add_date=CURRENT_DATE();

END

|

delimiter ;

insert into t_goods(id,goods_name,quantity) values(1,'apple',50);

select * fromt_goods;

2、删除触发器

DROP TRIGGER [schema_name.]trigger_name；

drop trigger t_trig;

3、查看触发器

SHOW TRIGGERS [ FROM DBNAME [ like '' ] ];

show triggers;

二、一个具体的实例

1.首先我们来创建两张表

-- 商品表

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

);

2.向商品表插入数据

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;

现在来创建一个触发器：当生成一个订单（卖出商品了），即向订单表o中插入一条记录时，更新商品表g中相应商品的剩余数量

create trigger tg1

after insert on o

for each row

begin

update g set num=num-3 where id=1;

end

-- 商品1卖出了3件

insert into o(gid,much) values(1,3);  -- 会发现商品1的数量变为7了

说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

   但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：

insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。

我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。

对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

现在来更改上面的触发器

drop trigger tg1;  -- 同一个表上不能创建多个触发时间和触发事件都相同并作用于同一张表的触发器，所以先删除tg1

create trigger tg2

after insert on o

for each row

begin

update g set num=num-new.much where id=new.gid;

end

-- 再来测试。。

insert into o(gid,much) values(2,3);  -- 发现商品2的数量变为7了

考虑另一种情况：当用户撤销一个订单的时候，我们这边直接删除一个订单，这时就需要把对应的商品数量再加回去

分析：

监视地点：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.gid;(或：where id = new.gid）

end

测试：

update o set much = 5 where oid = 1

我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。

三、before和after的区别

假设：假设商品表有商品1，数量是10；

我们往订单表插入一条记录：

insert into o(gid,much) values(1,20);

会发现商品1的数量变为-10了。这就是问题的所在，因为我们之前创建的触发器是after,也就是说触发的语句是在插入订单记录之后才执行的，这样我们就无法判断新插入订单的购买数量。

先讲一下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

执行完，把之前创建的after触发器删掉，再来插入一条订单记录：

insert into o(gid,much) valus(1,20)

执行完会发现订单记录的数量变为10，商品1的数量变为0了，就不会出现负数了。

另一个区别：

before：(insert、update)可以对new进行修改。

after不能对new进行修改。

两者都不能修改old数据；对于DELETE语句，只有old才合法。

举例说明：

mysql> select * from t1;

+------+------+
| id   | name |
+------+------+
|   1 |   aa  |
+------+------+

mysql> select * from t2;

Empty set (0.00 sec)

before触发器代码：

CREATE TRIGGER t1_before BEFORE update ON t1

FOR EACH ROW
BEGIN
 set new.id=new.id+11;
 set new.name=upper(new.name);  -- 转换为大写字母
 insert into t2 values(new.id,new.name);
END

mysql> update t1 set name='bbs' where id=1;

mysql> select * from t1;

+------+------+
| id   | name |
+------+------+
|   12 | BBS  |
+------+------+

mysql> select * from t2;

+------+------+
| id   | name |
+------+------+
|   12 | BBS  |
+------+------+

NEW值，已经被修改。

after触发器代码：

CREATE TRIGGER t1_after AFTER update ON t1

FOR EACH ROW
BEGIN
 set new.id=new.id+11;
 set new.name=upper(new.name);
 insert into t2 values(new.id,new.name);
END

运行时会报错：

[Err] 1362 - Updating of NEW row is not allowed in after trigger

由于是after触发器，不能对NEW值修改，所以报错。

所以修改after触发器：

drop trigger if exists t1_after;

CREATE TRIGGER t1_after AFTER update ON t1
FOR EACH ROW
BEGIN
 insert into t2 values(old.id,old.name);

-- 或insert into t2 values(new.id,new.name);都能运行

END

mysql> select * from t1;

+------+------+
| id   | name |
+------+------+
|   1 |   aa  |
+------+------+

mysql> select * from t2;

Empty set (0.00 sec)

mysql> update t1 set name='bbs' where id=1;

mysql> select * from t1;

+------+------+
| id   | name |
+------+------+
|    1 | bbs  |
+------+------+

mysql> select * from t2;

+------+------+
| id   | name |
+------+------+
|    1 | aa   |
+------+------+