实验五：触发器和存储过程

实验五：触发器和存储过程
一．实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。

二．实验内容：
有一个小型的图书管理数据库，包含的表为：
bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表
borrowcard（cardid，ownername）；--借书证表
borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表
写一个存储过程，实现借书操作，要求有事务处理。

（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。

（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。

（3）要求用触发器实现表的完整性控制。

三、操作与运行
1.创建图书数据库：
create table bookstore
(bookid int not null primary key,
bookname char(20),
bookauthor char(20),
purchasedate datetime,
state char(4)
)
create table borrowcard
(cardid int not null primary key,
ownername char(20)
)
create table borrowlog
(cardid int not null,
bookid int not null,
borrowdate datetime,
returndate datetime,
primary key(cardid,bookid),
---foreign key(cardid)references borrowcard(cardid), ---foreign key(bookid)references bookstore(bookid) )
通过以上语句，可以看到数据库中的表建立成功。

2.创建存储过程：
create proc book_borrow
@mycardid_in int,
@mybookid_in int,
@str_out char(30) output
as
begin
if not exists(select * from borrowcard where cardid=@mycardid_in) begin
set @str_out='该读者不存在'
return
end
if(select state from bookstore where bookid=@mybookid_in)='借出' begin
set @str_out='该书以借出'
end
begin tran
insert into borrowlog values(@mycardid_in,@mybookid_in,get date(),null)
if @@error>0
begin
rollback tran
set @str_out='执行过程中遇到错误！'
return
end
update bookstore set state='借出' where bookid=@mybookid_i n
if @@error>0
begin
rollback tran
set @str_out='执行过程中遇到错误!' return
end
if @@error=0
begin
commit tran
set @str_out='借书成功!'
return 1
else
begin
rollback tran
set @str_out='执行过程中遇到错误!' return
end
End
查看执行结果：
执行存储过程：
当违反参照完整性时：
declare @str_out char(30)
exec book_borrow 1114060119,106,@str_out output print '执行情况
' + @str_out
当图书已借出时执行结果会是：
declare @str_out char(30)
exec book_borrow 1114060114,102,@str_out output print '执行情况
' + @str_out
当正常执行时（即不违反完整性时）：
原先的借书记录有：
正常借书时：
declare @str_out char(30)
exec book_borrow 1114060116,105,@str_out output print '执行情况
' + @str_out
查看借书记录：
查看图书在库状态：
由此可知借书成功。

3.创建触发器：
create trigger delete_borrowlog
on bookstore
for delete
as begin
delete from borrowlog where bookid in(select bookid from d eleted)
End
执行：
delete
from bookstore
where bookid='102'
结果：
查看结束记录情况：
可知删除触发器创建成功。

create trigger update_borrowlog
on bookstore
for update
as begin
declare @old_bookid int,@new_bookid int
select @old_bookid=bookid from deleted
select @new_bookid=bookid from inserted
update borrowlog set bookid=@new_bookid where bookid=@old_ bookid
End
执行：
update bookstore
set bookid='119'
where bookid='105'
结果：
可见更新触发器创建成功。

四．问题及解决：
创建存储过程时不知道如何检查违反完整性的操作，最后知道可以用if@@error>0来检查。

创建触发器时刚开始不能创建成，根据提示知道原来在建表时已经设置了外键，所以弃掉就可以了。

五．思考题：
如何通过系统的设置实现类似的功能，而不需触发器？答：可以使用企业管理其中可视化的建表方法，也可以使用sql语句来在表中增加外键约束就可以了。

六．实验总结：
在这次试验中，我对存储过程和触发器有了一定的了解，首先对于存储过程在有输出变量时创建和执行时都需要声明，另外还要考虑到表的完整性规则，需要有检查的条件，对于存储过程需要有rollback操作来保证其正确性，然后对于触发器在创建时表中不能有外键约束，不然不能执行。