触发器(trigger):在监视表发生某些操作前或者操作后,触发某些操作。(不能监视select操作)
触发器创建语法四要素:监视表(table) + 监视事件(insert/update/delete) + 触发时间(after/before) + 触发事件(insert/update/delete)
语法:
create trigger triggerName
after/before insert/update/delete on tablename
for each row #这句话在mysql是固定的
begin
sql语句;
end;
创建触发器前需要修改默认的结束符,不然触发器没输入完全,MySQL就以为我们输入完成了
mysql> delimiter $
将默认的分号结束符修改为$,退出前记得修改回原来的结束符。
首先创建两张表用以实验:
创建物品表
create table tb_goods(
gid int not null primary key auto_increment,
name varchar(10) not null default '',
num int unsigned not null default 0
) default charset=utf8;
insert into tb_goods (name, num) values ("猪", 15),("羊", 20), ("狗", 10), ("猫", 5);
创建订单表:
create table tb_order(
oid int not null auto_increment,
gid int not null,
num int unsigned not null default 0,
constraint mkey primary key (oid),
foreign key (gid) references tb_goods(gid)
)default charset=utf8;
创建触发器:
触发器1
create trigger tg1 after insert on tb_order
for each row
begin
update tb_goods set num=num-
new.num where gid=
new.gid;
end$
触发器2
create trigger tg2 after delete on tb_order
for each row
begin
update tb_goods set num = num + old.much where gid = old.gid;(注意这边的变化)
end$
触发器3
create trigger tg3 after update on tb_order
for each row
begin
#update tb_goods set num = num+old.much-new.much where id = old/new.gid; #对于只更新数据的可以只用一行语句
update tb_goods set num = num + old.num where gid = old.gid;
update tb_goods set num = num - new.num where gid = new.gid;
end$
当触发器操作失败时,针对on后面的表的操作也将失败
对于insert插入后的数据行用new代替,对于delete删除的行用old代替,对于update,更新前的行用old代替,更新后的行用new代替