【mysql】【触发器使用】
首先先修改一下结束符delimiter $
创建触发器
1、增加类
create trigger triggerName1
after insert on buy
for each row
begin
update sell set sum = sum - new.sum where animal = new.animal;
end$
这里的triggerName1是创建触发器的名,buy是要插入的数据表名,begin和嗯对之间的是sql语句,new点表示新插入的行。
下面是使用触发器
2、删除
create trigger triggerName2
after delete on buy
for each row
begin
update sell set sum = sum + old.sum where animal = old.animal;
end$
使用触发器
3、修改
create trigger triggerName3
after update on buy
for each row
begin
update sell set sum = sum + old.sum - new.sum where animal = old.animal;
end$
使用触发器
查看触发器
show triggers$(这里的$是之前修改的结束符)删除触发器
以上用的都是after,下面来用before
create trigger triggerName4
before insert on buy
for each row
begin
if new.sum > 5 then
set new.sum=5;
end if;
update sell set sum = sum - new.sum where animal = new.animal;
end$
使用触发器