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

    --借书开始(1.修改书的状态,2.修改改读者的借书数量,3.向Borrow表中插入数据)

    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'

--由于bk2017002已经被借出去了,所以会出现不在馆的消息

 

 

3、带输入参数的存储过程:

创建一个存储过程,实现还书功能;

 1 create procedure usp_ReturnBook
 2 
 3        @rdID char(9),
 4 
 5        @bkID char(9)
 6 
 7 as
 8 
 9        --还书(1.修改书的状态,2.修改读者的借书数量,3.在Borrow表中删除这条借书纪录)
10 
11        update Book set bkState = 1
12 
13        where bkID = @bkID
14 
15  
16 
17        update Reader set rdBorrowQty = rdBorrowQty - 1
18 
19        where rdType = (select rdType from Reader where rdID = @rdID)
20 
21  
22 
23        delete from Borrow
24 
25        where rdID = @rdID and bkID = @bkID
26 
27 --调用
28 
29 exec usp_ReturnBook 'rd2017001','bk2017002'

 

 

4、带输入参数和输出参数的存储过程:

创建一个存储过程,输入读者的编号,输出该读者的姓名;

 1 create procedure usp_GetName
 2 
 3     @rdID char(9),
 4 
 5     @rdName varchar(20) output
 6 
 7 as
 8 
 9     select @rdName = rdName from Reader
10 
11     where rdID = @rdID
12 
13 go
14 
15 --调用
16 
17 declare @rdName varchar(20)
18 
19 exec usp_GetName 'rd2017001',@rdName output
20 
21 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。

 1 alter trigger tri_Insert on Borrow after insert
 2 
 3 as
 4 
 5     --先要判断该书在不在馆
 6 
 7     if not exists(select bkState from Book, inserted where Book.bkID = inserted.bkID)
 8 
 9     begin
10 
11         raiserror('该书不在馆,无法插入!',10,1)
12 
13         return
14 
15     end
16 
17  
18 
19     update Reader set rdBorrowQty = rdBorrowQty + 1
20 
21     from Reader, inserted
22 
23     where Reader.rdID = inserted.rdID
24 
25  
26 
27     update Book set bkState = 0
28 
29     from Book, inserted
30 
31     where Book.bkID = inserted.bkID
32 
33  
34 
35 --调用:
36 
37 insert into Borrow values ('rd2017004','bk2017004',GETDATE(),30,null)

 

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

 

posted @ 2017-04-27 13:39  少年闰土  阅读(6113)  评论(0编辑  收藏  举报