mysql基础三 触发器

商品表-- Table "goods" DDL：

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

订单表-- Table "orders" DDL：

CREATE TABLE orders (
oid int(11) NOT NULL,
gid int(11) DEFAULT NULL,
much smallint(6) DEFAULT NULL,
create_time datetime DEFAULT NULL,
PRIMARY KEY (oid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一、insert触发器：
(1)、after触发器：
需求：客户购买某个商品后，商品的库存数量自动减少。

create trigger trigerafterInsertOrder
after insert
on orders
for each row
begin

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

end;

(2)、before触发器：
需求：客户购买某个商品后，商品的库存数量自动减少。
同时订单表的create_time为当前时间。

create trigger trigerbeforeInsertOrder
before insert
on orders
for each row
begin
update goods set num=num-new.much where gid=new.gid;
set new.create_time=now();

end;

注意：new 表示orders表新增行。

二、delete触发器：

需求：客户取消订单后，商品表的库存数量自动增加。

create trigger triggerDeleteOrder
after delete
on orders
for each row

begin

update goods set num=num+old.much where gid=old.gid

end;

注意：old 表示orders表删除行。

三、update 触发器：

需求：客户修改订单购买的数量，商品表的库存数量自动改变。

create trigger triggerUpdateOrder
before update
on orders
for each row

begin

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

end;

四、after和before触发器的区别：

1、after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作。

2、before是先完成触发，再增删改，触发的语句先于监视的增删改。

例1：对于上面的trigerbeforeInsertOrder这个触发器，因为同时要对orders表的create_time字段赋值。
对字段create_time的赋值操作要早于insert，因此使用before触发器。
注意：如果此时使用after触发器会报Updating of new row is not allowed in after trigger 错误。

例2：爆仓业务的检查即客户购买某个商品，购买商品的数量如果超过库存数量则购买数量为库存数量，同时商品表的库存数量自动改变。

create trigger triggerBeforeInsertOrder before insert on orders for each row

begin

declare kcNum int;

select num into kcNum from goods where gid=new.gid;

if new.much>kcNum then
set new.much=kuNum;
end if;

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

end;

注意：如果此时使用after触发器会报Updating of new row is not allowed in after trigger 错误。

五、触发器引用行变量

下图一目了然地描述了触发器的行变量new和old。