最近数据库方面遇到了瓶颈,查数据写出来的sql语句自己都觉得效率不高,但也不知道怎么去优化,于是乎决定要把mysql系统的学一遍,今天学到了mysql的触发器。下面咱就说下俺的理解。
1.什么是触发器?为什么要用?
mysql触发器说白了就是一段嵌入到mysql的程序,在你对表进行增删改时触发该程序,同时响应另一张表做出相同的操作。
举个例子来说:有一张商品表和一张订单表,当购买商品时,要相应的往订单表里插入数据,同时商品表里的商品数量要减少。这个我们在程序里可以解决,但是用触发器该怎么做呢?
2.触发器的创建
首先创建两张表吧,goods表和order表
create table goods( gid int unsigned not null auto_increment primary key, name varchar(30) not null default '', num int )engine innodb charset utf8;
create table order( oid int unsigned not null auto_increment primary key, gid int, amount int )engine innodb charset utf8;
好了,简单的建了两张表。下面开始建触发器
①触发器的五要素
触发器的名称、触发时间、监听的事件、触发地点、触发的事件
delimiter $ create trigger p1 #触发器名称 before(after) insert #触发时间before或者after,监听的insert事件 on order #触发的地点,order表 for each row #mysql是行级触发器 begin update goods set num=num-new.amount where gid=new.gid;#触发事件 new或old可以用来替代原来的和新的数据 end$
那么当向order表里插入数据时,该触发器就会响应,改变goods表里对应的商品数量。
上面的例子,如果商品表里的商品数量为负了怎么办?如果为负,假如让order表里的amount为剩余的商品数量。下面创建触发器
delimiter $ create trigger p2 after insert on order for each row begin declare snum int; select num into snum from goods where gid=new.gid; if new.amount>snum #如果商品数量不足,则有多少购买多少 set new.amount=snum; end if; update goods set num=num-new.amount where gid=new.gid; end$
那么当插入数据时,会不会正常执行呢?不好意思,结果报错了,报了什么错?
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
意思是不允许在事后触发器修改新的记录,也就是已经insert数据了,又试图立马修改insert后的数据,这是不被允许的,那怎么办呢?
我们只需要将上面的after insert改为before insert,即事前修改就行了。