1.首先新建两张表
create table tb_goods(
id int(11) not null primary key ,
goods_name varchar(128) not null,
num int(11) default'0' );
create table tb_order(
orderid int(11) not null primary key,
goods_id int(11) ,
onum int )
2.触发器语法
create trigger tname
after/before insert/update/delete on tb_goods
for each row //这句是固定的
Begin
//sql语句
//对新增的行用NEW表示 对列 用 NEW.列名 来表示
End
3.example:
delimiter $ //遇到$才结束 避免与分号冲突
create trigger tr2
after insert on tb_order
for each row
begin
update tb_goods set num=num-NEW.onum where id = NEW.orderid;
end$
create trigger tr3
after delete on tb_order
for each row
begin
update tb_goods set num = num + OLD.onum where id = OLD.goods_id ; //对修改之前的行或者删除的行用OLD表示
end$
//update tb_goods set num=num+OLD.onum-NEW.onum where id=OLD.orderid