SQL Server 中的存储过程和触发器实例
针对简易图书管理数据库 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)
学习过程之中难免存在错误，望多多指出。
页:
[1]