一、Mysql学习笔记-------------触发器
定义:触发器是一类特殊的事物,可以监视某种数据操作,如增删改,并触发相关操作,如增删改
触发器四要点:
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
例子:
商品表
CREATE TABLE `goods` (
`gid` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`num` smallint(6) DEFAULT NULL
);
订单表
CREATE TABLE `orders` (
`oid` int(11) DEFAULT NULL,
`gid` int(11) DEFAULT NULL,
`num` smallint(6) DEFAULT NULL
)
1.下单后,对应商品的数量减去下单数量
create trigger t1
after insert on orders
for each row
begin
update goods set num = num - new.num where gid = new.gid;
end;
2.删除订单后,对应商品的数量加上下单数量
create trigger t2
after delete on orders
for each row
begin
update goods set num = num + old.num where gid = old.gid
end;
3.只修改订单数量
create trigger t3
before update on orders
for each row
begin
update goods set num = num + old.num - new.num where gid = old.gid;
end;
4.当下订单时,对应商品的数量减去下单数量,并检测是否爆仓,如果爆仓则只修改库存里的
create trigger t4
before on orders
for each row
begin
declare rnum int;
select num into rnum from goods where gid = new.gid;
if new.num>rnum then
set new.num = rnum;
end;
update goods set num = num - new.num where gid = new.gid;
end;
Mysql学习笔记(一)
最新推荐文章于 2024-09-12 01:15:37 发布