mysq触发器:监视某种情况,并触发某种操作。
触发器创建语法四要素:
1.监视地点(table)2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)
语法:
create trigger triggerName
after/before insert/update/delete on 表名
for each row #这句话在mysql是固定的
begin
sql语句;
end;

————————–
例子

增加 order为订单表 goods商品表
delimiter $ #修改定界符
create trigger t1 #创建一个t1
before insert on orders # 在插入之前操作
for each row #查所有行
begin #开始
declare nums int ; #定义一个nums
select num into nums from goods where id = new.goodsid ; #查询值放在nums里
if new.much > nums then #判断
set new.much = nums ;
end if ;
update goods set num = num – new.much where id = new.goodsid ; #执行
end$
———————————–
删除
create trigger t2
after delete on orders
for each row
begin
update goods set num = num + old.much where id = old.goodsid;
end $
———————–
修改
create trigger t3
before update on orders
for each row
begin
declare nums int ;
select num into nums from goods where id = new.goodsid ;
if new.much – old.much > nums then
set new.much = nums + old.much;
end if ;
update goods set num = num + old.much – new.much where id = new.goodsid;
end $