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。