2015年12月25日 Mysql 浏览(229)

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;


用户头像