mysql的触发器

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

一、触发器的作用

监视某种情况并触发某种操作

备注：我在网上也看了很多概念性的东西，但是感觉比较啰嗦，我感觉运维人员只要能大体上把这个用简短的话描述出来就可以了。下面是我在网上找的触发器的作用

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
  # 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
  # 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。
2.审计。可以跟踪用户对数据库的操作。   
  # 审计用户操作数据库的语句。
  # 把用户对数据库的更新写入审计表。
3.实现复杂的数据完整性规则
  # 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
  # 提供可变的缺省值。
4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
  # 在修改或删除时级联修改或删除其它表中的与之匹配的行。
  # 在修改或删除时把其它表中的与之匹配的行设成NULL值。
  # 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
  # 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。
5.同步实时地复制表中的数据。
6.自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

二、触发器的四要素

监控地点
监控事件(insert/update/delete)
触发时间(after/before)
触发事件(insert/update/delete)

三、创建触发器的语法

create trigger triggerName(触发器名称)
after/before insert/update/delete on tableName(表名)
for each row    #这句话是固定的
begin
sql语句;        #一句或多句，insert/update/delete范围内
end$

四、创建练习触发器的表

create table shop (
id smallint unsigned primary key auto_increment,
name varchar(10) not null,
num int unsigned not null
) charset utf8;

create table calculate (
aid int unsigned primary key,
gid int unsigned not null,
much int unsigned not null
) charset utf8;

insert into shop values 
(1,'苹果',50),
(2,'香蕉',75),
(3,'菠萝',30),
(4,'哈密瓜',46);

结构图

五、触发器的练习

1、首先，在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为$或者其他符号。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。

mysql> delimiter  $

2、在 calculate 表进行插入(insert)操作之后，更新shop表中的num
例如：购买10个香蕉后，shop表里面的num列的数量少10

create trigger tg1
after insert on calculate
for each row
begin
update shop set num = num -10 where id = 2;
end$

购买10个香蕉：insert into calculate values (1,2,10)$

备注：对于上述，我们如果购买其他的商品，一直都是香蕉对应的列的数量减少

3、添加订单，对应的库存列减少
对于insert而言，新增的行用 new 来表示，行中的每一列用 new.列名 来表示。

create trigger tg2
after insert on calculate
for each row
begin
update shop set num = num - new.much  where id = new.gid;
end$

购买12个苹果：insert into calculate values (1,1,12)$

购买5个菠萝：insert into calculate values (2,3,5)$

4、删除一个订单，库存相应增加
对于delete而言，删除的行用 old 来表示，行中的每一列用 old.列名 来表示。

create trigger tg3
after delete on calculate
for each row
begin
update shop set num = num + old.much where id = old.gid;
end$

不想买菠萝了，删除订单：delete from calculate where aid = 2$

5、修改订单数量时，库存相应的变化
对于update而言，新增的行用 new 来表示，行中的每一列用 new.列名 来表示；删除的行用 old 来表示，行中的每一列用 old.列名 来表示。

create trigger tg4
after update on calculate
for each row
begin
update shop set num = num + old.much - new.much where id = old.gid;
end$

备注：在这里用 old.gid 和 new.gid 是一样的

想调整小购买苹果的数量
12个有点多了，想买少点

6、触发器里面before和after的区别
after是先完成数据的增删改再触发；触发的语句晚于监视的增删改，无法影响前面的增删改动作。
before是先完成触，再增删改；触发的语句先于监视的增删改，会影响前面的增删改动作。

例如：对于所下订单进行判断，如果订单数量 > 20，就认为是恶意订单，强制把这个订单修改成20.

create trigger tg5
before update on calculate
for each row
begin
if new.much > 20 then
    set new.much = 20;
end if;
    update shop set num = num - new.much where id = new.gid;
end$

在更新订单前做一定的限制

六、删除触发器

查看触发器
mysql> show triggers;
删除触发器
mysql> drop trigger triggerName;

备注：查看或者删除触发器，需要进到库里面，即 use 库名