SQL Server 中的存储过程和触发器实例

James

　　针对简易图书管理数据库 BooksDB，完成下述功能。
　　1、不带参数的存储过程：创建一个存储过程，查看所有读者的姓名、可借本数、可借天数和已借 书本数。
　　2、带输入参数的存储过程：创建一个存储过程，实现借书功能；
　　3、带输入参数的存储过程：创建一个存储过程，实现还书功能；
　　4、带输入参数和输出参数的存储过程：创建一个存储过程，输入读者的编号，输出该读者的姓名；
　　5、创建 DDL 触发器，禁止用户修改 BooksDB 数据库中的表；
　　6、测试 AFTER 触发器，理解 INSERTED 表和 DELETED 表的作用；
　　BooksDB中的几个表：

　　1、不带参数的存储过程：
　　创建一个存储过程，查看所有读者的姓名、可借本数、可借天数和已借书本数。
　　

create procedure usp_GetInfo　　

as　　select rdName, canLendQty, canLendDay, rdBorrowQty from Reader, ReaderType
　　where Reader.rdType = ReaderType.rdType
　　
调用的时候：
　　
exec usp_GetInfo
　　

　　2、带输入参数的存储过程：
　　创建一个存储过程，实现借书功能；
　　

create procedure usp_BorrowBook　　

@rdID char(9),　　

@bkID char(9)　　

　　

as　　

　　

　　if not exists(select * from Book where @bkID in (select bkID from Book))
　　

　　begin
　　

　　raiserror('图书馆没有该书，借阅失败',10,1)
　　

　　return
　　

　　end
　　

　　

　　declare @bkState int
　　

　　select @bkState = bkState from Book
　　

　　where bkID = @bkID
　　

　　if @bkState = 0
　　

　　begin
　　

　　raiserror('该书不在馆，无法借阅',10,1)
　　

　　return
　　

　　end
　　

　　

　　declare @rdBorrowQty int , @canLendQty int
　　

　　select @rdBorrowQty = rdBorrowQty from Reader
　　

　　where rdID = @rdID
　　

　　select @canLendQty =  canLendQty from ReaderType
　　

　　where rdType = (select rdType from Reader where rdID = @rdID)
　　

　　if @rdBorrowQty = @canLendQty
　　

　　begin
　　

　　raiserror('抱歉！你所借书的数量已经达到最大借书数量！借阅失败！',10,1)
　　

　　return
　　

　　end
　　

　　

　　update Book
　　

　　set bkState = 0
　　

　　where bkID = @bkID
　　

　　

　　update Reader
　　

　　set rdBorrowQty = rdBorrowQty + 1
　　

　　where rdID = @rdID
　　

　　

　　declare @canLendDay int
　　

　　select @canLendDay = canLendDay from ReaderType
　　

　　where rdType = (select rdType from Reader where rdID = @rdID)
　　

　　insert into Borrow values(@rdID,@bkID,GETDATE(),DATEADD(dd,@canLendDay,GETDATE()),null)
　　

　　

　　

　　
exec usp_BorrowBook 'rd2017001','bk2017006'
　　

　　

exec usp_BorrowBook 'rd2017001','bk2017002'　　

　　
exec usp_BorrowBook 'rd2017002','bk2017002'
　　

　　

　　

　　3、带输入参数的存储过程：
　　创建一个存储过程，实现还书功能；
　　

create procedure usp_ReturnBook　　

　　
        @rdID char(9),
　　

　　
        @bkID char(9)
　　

　　
as
　　

　　
        
　　

　　
        update Book set bkState = 1
　　

　　
        where bkID = @bkID
　　

　　
  
　　

　　
        update Reader set rdBorrowQty = rdBorrowQty - 1
　　

　　
        where rdType = (select rdType from Reader where rdID = @rdID)
　　

　　
  
　　

　　
        delete from Borrow
　　

　　
        where rdID = @rdID and bkID = @bkID
　　

　　

　　

　　
exec usp_ReturnBook 'rd2017001','bk2017002'
　　

　　4、带输入参数和输出参数的存储过程：
　　创建一个存储过程，输入读者的编号，输出该读者的姓名；
　　

create procedure usp_GetName　　

　　
     @rdID char(9),
　　

　　
     @rdName varchar(20) output
　　

　　
as
　　

　　
     select @rdName = rdName from Reader
　　

　　
     where rdID = @rdID
　　

　　
go
　　

　　

　　

　　
declare @rdName varchar(20)
　　

　　
exec usp_GetName 'rd2017001',@rdName output
　　

　　
select @rdName 姓名
　　

　　5、创建DDL 触发器：
　　禁止用户修改 BooksDB 数据库中的表；、
　　

create trigger tri_OnBookDB on database　　

　　
for ddl_table_events
　　

　　
as
　　

　　print '无法在数据库BookDB中创建,删除,修改表!!'
　　

　　rollback
　　

　　

　　

　　
create table Test (a int,b char(6))
　　

　　
drop table Borrow
　　

　　6、测试AFTER 触发器，理解INSERTED 表和DELETED 表的作用；
　　Instead of触发器:
　　一开始Borrow表中有这样一条纪录：

　　当创建Instead of 触发器后：
　　

create trigger tri_InsteadOf on Borrow instead of delete　　

　　
as
　　

　　select rdID, bkID from deleted
　　

　　

　　

　　
delete from Borrow where rdID = 'rd2017001'
　　

　　deleted表中会出现：

　　但是在Borrow表中这条纪录并没有被删除。原因是触发器中代替了所要执行的delete操作。
　　After 触发器：
　　当向Borrow表中插入一条纪录时候，应修改Reader表中的rdBorrowQty加1，而在Book表中修改相应的书的状态为0。
　　

>
　　
as
　　

　　
     
　　

　　
     if not exists(select bkState from Book, inserted where Book.bkID = inserted.bkID)
　　

　　
     begin
　　

　　
         raiserror('该书不在馆，无法插入！',10,1)
　　

　　
         return
　　

　　
     end
　　

　　
  
　　

　　
     update Reader set rdBorrowQty = rdBorrowQty + 1
　　

　　
     from Reader, inserted
　　

　　
     where Reader.rdID = inserted.rdID
　　

　　
  
　　

　　
     update Book set bkState = 0
　　

　　
     from Book, inserted
　　

　　
     where Book.bkID = inserted.bkID
　　

　　
  
　　

　　

　　

　　
insert into Borrow values ('rd2017004','bk2017004',GETDATE(),30,null)
　　

　　学习过程之中难免存在错误，望多多指出。