select * from book
select * from borrow

--功能需求：在客戶借閱書籍的時候（客戶每次只能對同一種書借閱１本）
--	    借完之后book表中書的數量自動減１(方便操作)
--定義after觸發器下的insert操作觸發器
create trigger trg_InsertBorrow
on borrow
after insert
as
  update book set bookNum = bookNum - 1 where bookID = (select bookID from inserted)

--測試
insert into borrow values(3,'2006-4-1','2006-4-10',0)


--功能需求：在管理人員修改book中的數量的時候，要求最終的數量要＜５０本．
--定義after觸發器下的update操作觸發器
alter trigger trg_UpdateBorrow
on book
after update
as
  declare @num int
  select @num = bookNum from inserted
  if @num > 50
  begin
    raiserror ('總數>50',1,1)
    rollback transaction
  end
  

--測試
update book set bookNum = bookNum + 50 where bookID = 1



--功能需求：管理人員可能要刪除圖書的信息，如果此書有借閱的話，就不允許刪除圖書信息．
--定義after觸發器下的delete操作觸發器
alter trigger trg_DeleteBorrow
on book
after delete
as
  declare @r int
  select @r=isReturn from borrow where bookID = (select bookID from deleted)
  if (@r = 0)
  begin
    raiserror ('此書籍被借閱',1,1)
    rollback
  end
--測試
begin tran
delete from book where bookID = 3

rollback
select @@error

select * from book
select * from borrow
--------------------------------------------------------

select * from E1
select * from E2


--功能要求：刪除老員工，將老員工信息存到E2表中去
create trigger trg_DeleteE1
on E1
after delete
as
   declare @id int
   declare @name char(10)
   select @id = EID,@name = Ename from deleted
   insert into E2 values(@id,@name)

--測試
delete from E1 where EID = 1





--測試2
delete from E1 where EID > 1   --問題出現的原因？