Mysql学习——触发器

一、触发器的定义

触发器是一类特殊的事务，可以监视某种数据操作(insert/delete/update)，并触发相应操作(insert/delete/update)。

使用场景：

1、当向一张表中添加或删除记录时，需要在相关表中进行同步操作

2、当表上某列数据的值与其他表中的数据有联系时。

3、当需要对某张表进行跟踪时

二、触发器创建语法 —— 4要素

1、监视地点  table

2、监视事件  insert/update/delete

3、触发时间 after/before

4、触发事件 insert/update/delete

创建触发器的语法：

create trigger 触发器名称 

after/before (触发时间)

insert/update/delete (监视事件)

on 表名 (监视地址)

for each row

begin

sql1;

..

sqlN;

end;

设置mysql 最外层分割符  delimiter  $

查看已有触发器： show triggers

删除已有触发器：drop trigger  触发器名称

例子：

通过监视订单表的新增订单，减少商品表中对应商品的库存

商品表：

CREATE TABLE `good` (
  `gid` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  `num` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

订单表：

CREATE TABLE `ord` (
  `gid` int(11) DEFAULT NULL,
  `orderid` int(11) DEFAULT NULL,
  `much` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立监听器： new  对应新行   old  对应旧行

delimiter  $

create trigger good_num_trigger

after

insert

on ord

for each row

begin

update good set num = num - new.much where gid = new.gid;

end$

create trigger good_add_num

after

delete

on ord

for each row

begin

update good set num = num+old.much where gid =old.gid;

end$

delimiter  ;

建立监听器：在订单订购数量改变时，更新商品库存表中的库存 new  对应新行   old  对应旧行

delimiter  $

create trigger ord_update_much

before

update

on ord

for each row

begin

update good set num = num+old.much -new.much where gid =new.gid;

end$

delimiter  ;

建立监听器：判断插入的订单的much  是否大于订购商品的库存，如果大于库存，则调整该订单的订购数量为当前商品库存

delimiter  $

create trigger ord_check_much

before

insert

on ord

for each row

begin

declare 

rnum int;

select num into rnum from good where gid = new.gid;

if new.much > rnum then

set new.much = rnum;

end if;

update good set num = num -new.much where gid = new.gid;

end$

delimiter  ;

注意：for each row 在mysql 中的监听器必须有，在其他数据库中比如oracle中的监听器是可以缺失的

1、如果有 for each row ，则是行级监听器

执行的语句操作了n行数据，则该监听器被调用n次。

2、如果没有for each row，则是语句级监听器

按照监听到的语句数量，调用监听器