mysql基礎(chǔ)三 觸發(fā)器

商品表-- 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觸發(fā)器：
(1)、after觸發(fā)器：
需求：客戶購買某個(gè)商品后，商品的庫存數(shù)量自動減少。

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觸發(fā)器：
需求：客戶購買某個(gè)商品后，商品的庫存數(shù)量自動減少。
同時(shí)訂單表的create_time為當(dāng)前時(shí)間。

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觸發(fā)器：

需求：客戶取消訂單后，商品表的庫存數(shù)量自動增加。

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 觸發(fā)器：

需求：客戶修改訂單購買的數(shù)量，商品表的庫存數(shù)量自動改變。

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觸發(fā)器的區(qū)別：

1、after是先完成數(shù)據(jù)的增刪改，再觸發(fā)，觸發(fā)的語句晚于監(jiān)視的增刪改操作，無法影響前面的增刪改動作。

2、before是先完成觸發(fā)，再增刪改，觸發(fā)的語句先于監(jiān)視的增刪改。

例1：對于上面的trigerbeforeInsertOrder這個(gè)觸發(fā)器，因?yàn)橥瑫r(shí)要對orders表的create_time字段賦值。
對字段create_time的賦值操作要早于insert，因此使用before觸發(fā)器。
注意：如果此時(shí)使用after觸發(fā)器會報(bào)Updating of new row is not allowed in after trigger 錯(cuò)誤。

例2：爆倉業(yè)務(wù)的檢查即客戶購買某個(gè)商品，購買商品的數(shù)量如果超過庫存數(shù)量則購買數(shù)量為庫存數(shù)量，同時(shí)商品表的庫存數(shù)量自動改變。

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;

注意：如果此時(shí)使用after觸發(fā)器會報(bào)Updating of new row is not allowed in after trigger 錯(cuò)誤。

五、觸發(fā)器引用行變量

下圖一目了然地描述了觸發(fā)器的行變量new和old。