头歌 触发器答案

第1关：任务1：CTGU实验5_1-创建还书过程中的触发器

/*  1.	创建一个触发器tri_returnbook，当一本书被还回时，从LOAN表中删除相应的借阅记录，
将该学生借阅这本书记录添加到LoadHist表中；并检查是否有用户在等待预约这本书：
如有则将这本书的借阅状况修改为 已经预约，并按照预约的日期先后，将最先预约的预约状态改为T；
如果没有人预约，则应该将此书的借阅状况修改为“可以借阅且尚未借出”（修改状态字段值）*/


use library;
drop TRIGGER if EXISTS tri_returnbook;
delimiter //
 
######### Begin #########
create  trigger tri_returnbook
after delete on Loan 
for each row
Begin
declare a int;
declare b char(13);
insert into LoanHist(loanNo,bookNo,borrowDate,returnDate)
values (old.loanNo,old.bookNo,old.borrowDate,now());
select count(loanNo)into a
 from Books B join Reservation R  on B.ISBN=R.ISBN  WHERE old.bookNo=B.bookNo;
 if(a = 0)
  then update Books set bstatus = "0" where old.bookNo=Books.bookNo;
  end if;
  if(a!=0)
  then select ISBN into b
  from Books B where old.bookNo=B.bookNo;
  update Books set bstatus = "3" where old.bookNo=Books.bookNo;
  update Reservation set rstatus = "T" where ISBN=b;
  END if;
  END//
######## End #########
 
delimiter ;
 
 SELECT EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_ORDER,ACTION_ORIENTATION,ACTION_TIMING 
from information_schema.TRIGGERS
where TRIGGER_NAME='tri_returnbook';

第2关：任务2-CTGU实验5_2-创建借书触发器

/*2.	创建一个触发器tri_loan，当借书成功，修改该书的状态为“1”（代表已经借出）,
并检索是否有这个用户的预约该书的记录，如果有，则删除相应的预约记录*/ 
use library;
drop trigger if exists tri_loan;
delimiter //

######### Begin #########
create trigger tri_loan 
after insert on Loan 
for each row 
begin 
update Books set bstatus=1 where Books.bookNo=new.bookNo; 
if exists(select * from Reservation,Books where Reservation.ISBN=Books.ISBN and Books.bookNo=new.bookNo) then 
delete from Reservation where Reservation.loanNo=new.loanNo; 
end if; 
end;// 
######### End ##########

delimiter ;

能闻着味找到这，还是你小子行