闲来无事看看十八哥的 mysql高级视频 www.zixue.it
创建一个触发器
先把结束符改掉
delimiter $
create trigger t2 # t2是触发器名称
after # 触发的时候 before 和 after
insert # 触发的事件
ON ord# ON tablename 监视的表
for each row #这个是貌似是固定的写法;是说当上面的事件触发了几次;后面也要相应的处理几次;
begin
update goods set num=num-2 where id=1; #这个即是当触发之后执行的sql
end$
上面的num=num-2 id=1 都是固定死的 引入变量
create trigger t2
after
insert
ON ord
for each row
begin
update goods set num=num-new.num where id=new.gid; #new ,old 就是代表 ord表insert后和前;
end$
在看看 delete 和update事件;也加以理解 new和old
create trigger t3
after
delete
on ord
for each row
begin
update goods set num=num+old.num where id=old.gid;
end$
create trigger t4
before
update
on ord
for each row
begin
update goods set num=num+old.num-new.num where id=old.gid;
end$
触发器中加入判断;
//插入之前做判断; 注意要把t2删掉;
create trigger t5
before
insert
on ord
for each row
begin
declare
rnum int;
select num into rnum from goods where id=new.gid;
if new.num >= rnum then
set new.num=rnum;
end if;
update goods set num=num-new.num where id=new.gid;
end$
查看触发器 show triggers \G
删除触发器 delete trigger t1(触发器名称)