2.12 触发器trigger

触发器语法格式：

-- 增加村民记录的触发器
DROP TRIGGER if EXISTS `insert_villager`;
DELIMITER $
CREATE TRIGGER `insert_villager`
BEFORE INSERT 
ON `villager`
FOR EACH ROW
BEGIN
-- 根据出生年月日计算村民年龄
    SET new.villager_age = DATEDIFF(NOW(),new.villager_birthday)/365;
-- 村民所在的家庭人口数+1
  UPDATE `family` SET people_amount = people_amount + 1
    WHERE `family`.`F_ID` = new.`FK_FID`;
END $
DELIMITER;

drop TRIGGER if EXISTS `tri_returnbook`;
delimiter //
create TRIGGER `tri_returnbook`
after delete
on `Loan`
for each row
Begin
declare _ISBN char(13);
declare _loanNo varchar(16);
insert into LoanHist(loanNo,bookNo,borrowDate,returnDate) value(old.loanNo,old.bookNo,old.borrowDate,now());
SELECT r.ISBN,r.loanNo into _ISBN,_loanNo 
from Reservation as r,Books as b
where r.ISBN = b.ISBN and b.bookNo = old.bookNo
order by r.reservationDate
limit 0,1;
if(_ISBN is null) then
    update Books set bstatus = 0
    where bookNo = old.bookNo;
else 
    update Reservation set rstatus = 'T'
    where ISBN = _ISBN and loanNo = _loanNo;
    update Books set bstatus = 3
    where bookNo = old.bookNo;
end if;
end//
delimiter ;