mysql之触发器

触发器
作用：监视某种情况（增删改）并发除某种操作（增删改）
触发器四要素：
监视地点，监视事件，触发事件，触发事件

创建触发器的语法
create trigger 触发器名
after/before
insert/delete/update
for each row 
一句或多聚insert/delete/update语句

在触发器中引用新行的值
对于insert 而言，新增的行用new来表示，行中的每一列的值，用new.列名表示
对于delete而言，原本有一行，后来被删除，想要引用被删除的这一行的，用old表示，old.列名引用被删除行的列中的值
对于update而言，修改前的行用old表示，old.列名引用行中的值，修改后的行用new，new.列名引用修改后的值


删除出发器：
drop trigger 触发器名；

查看触发器
show triggers；


#商品
create table g(
gid int,
gname VARCHAR(10),
num  int 
)
#下单
create table o(
oid int,
ogid int,
much int
)
SHOW TABLES
INSERT INTO g VALUES(1,'84消毒',100);
INSERT INTO g VALUES(2,'口罩',1000);
INSERT INTO g VALUES(3,'n95口罩',300);
INSERT INTO g VALUES(4,'酒精',550);
SELECT * FROM g;
SELECT *FROM o ;
DROP table o ;
DROP table g;
触发器1,在下订单时，商品库存减少
create trigger tg1
after insert on o
for each row
update g set num=num-new.much where gid=new.ogid;

INSERT INTO o VALUES(1,1,50);
SELECT * FROM g;
SELECT *FROM o ;

触发器2 ，取消下单，库存增加
create trigger tg2 
AFTER 
delete on o 
for each row 
update g set num = num +old.much 
where gid=old.ogid;

delete from o WHERE oid = 1;
SELECT * FROM g;
SELECT *FROM o ;


#触发器3 修改订单
create trigger tg3
after 
update on o 
for each row 
update g set num = num +old.much -new.much
where gid=old.ogid;


INSERT INTO o VALUES(1,1,50);
INSERT INTO o VALUES(2,2,150);

SELECT * FROM g;
SELECT *FROM o ;
update o set much =40 
WHERE oid = 2;
SELECT * FROM g;
SELECT *FROM o ;

SHOW TRIGGERS;
drop trigger tg1 ;
drop trigger tg2 ;
drop trigger tg3 ;
show TRIGGERs

#对于下单进行判断，如果订单数量>5，认为是恶意订单，强制把该订单的购买数量改为5
delimiter $
create trigger tg4
BEFORE 
INSERT on o
for EACH ROW
BEGIN
if new.much >5 then
		set new.much = 5;
end IF;
update g set num=num-new.much
where gid=new.ogid;
end $

SELECT * FROM g;
SELECT *FROM o ;
INSERT INTO o VALUES(3,3,50);
SELECT * FROM g;
SELECT *FROM o ;