mysql 触发器的编写_MySql编写触发器的若干示例

在使用mysql的过程中，我们希望对某些敏感数据加上触发器来生成日志，用来监控核心数据的变更情况。那么Mysql的触发器如何编写呢？

示例1:

create trigger set_status after insert on bookborrowinfo

for each row BEGIN

set @bookid=new.bookid;

set @outnum=(select outnum from bookstatus where bookid=@bookid);

set @booknum=(select booknum from book where id=@bookid);

if @outnum is null then

insert into bookstatus(bookid,outnum,status) values(@bookid,1,1);

else

update bookstatus set outnum=@outnum+1 where bookid=@bookid;

end if;

set @outnum=@outnum+1;

if @outnum>=@booknum then

update bookstatus set status=0 where bookid=@bookid;

end if;

END;

示例2:

create trigger set_returnstatus after delete on bookborrowinfo

for each row BEGIN

set @bookid=old.bookid;

set @outnum=(select outnum from bookstatus where bookid=@bookid);

update bookstatus set outnum=@outnum-1,status=1 where bookid=@bookid;

END;

示例3:

create trigger set_status after insert on bookborrowinfo

for each row BEGIN

declare booknum_0 int;

declare bookid_0 int;

set bookid_0=new.bookid;

set @outnum=(select outnum from bookstatus where bookid=bookid_0);

set booknum_0=(select booknum from book where id=bookid_0);

if @outnum_0<=0 then

insert into bookstatus(bookid,outnum,status) values(bookid_0,1,1);

else

update bookstatus set outnum=outnum+1 where bookid=bookid_0;

end if;

END;