触发器高级使用.sql

本代码简单明了

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   --问题出现的原因？