实验11 触发器

实验11 触发器
一、学习要求
(1) 通过实践理解触发器的概念、作用、优点。

(2) 掌握利用界面方式创建、修改和删除不同类型的触发器。

(3) 掌握利用命令方式创建、修改和删除不同类型的触发器。

二、实验内容
在已建立的TSGL***数据库的基础上，按如下要求对数据库进行操作，保存操作代码和截图，命名要求同前。

1.创建一触发器，当向借阅表插入一条记录时，检查该记录的借书证号在读者表中是否存在，检查该记录图书的ISBN号在图书表中是否存在，以及图书的库存量是否大于0,若有一项为否，则不允许插入，并提示操作失败。

create trigger Lend_insert
on Lend
after insert
as
declare@借书证号char(12),@ISBN编号char(16)
select@借书证号=借书证号from inserted
select@isbn编号=(select@ISBN编号from reader,lend where@借书证号
=reader.借书证号and reader.借书证号=lend.借书证号)
if(@借书证号not in(select*from reader.借书证号)
or@isbn编号not in(select*from book.isbn编号)
or exists(select*from book where库存量<=0))
begin
print'不允许插入'
end
2.创建一触发器，当删除读者表一条记录时，检查该记录的借书证号在借阅表中是否存在，如果存在，则不允许删除，并提示先归还在借图书。

create trigger delete_reader
on reader
for delete
as
declare@借书证号char(12)
select@借书证号=借书证号from inserted
if(@借书证号not in(select*from lend.借书证号))
begin
print'不允许删除，请先归还图书'
end
else
begin
print('删除成功')
end
3．创建一触发器，当读者借一本书（即向借阅表中插入一条记录）时，在读者表中，该读者的借书量应增加1，而图书表中该图书的库存量应减1。

create trigger table_insert
on reader after insert
as
begin
declare@借书证号char(12)
select@借书证号=借书证号from inserted
update reader set借阅数量=借阅数量+1
update book set库存量=库存量-1
end
4．创建一触发器，当读者还一本书（即向借阅表删除一条记录）时，在读者表中，该读者的借书量应减1，而图书表中该图书的库存量应加1。

create trigger table_delete
on reader after insert
as
begin
declare@借书证号char(12)
select@借书证号=借书证号from inserted
update reader set借阅数量=借阅数量-1
update book set库存量=库存量+1
end
5．创建一数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。

create trigger safety_datebase
on database
after drop_table
as
print'不能删除该表'
rollback transaction
6．创建一服务器作用域的DDL触发器，当删除一个库时，提示禁止该操作，然后回滚删除表的操作。

create trigger safety_server
on all server
after drop_database
as
print'不能删除该数据库'
rollback transaction
7.删除第3题创建的触发器，然后修改第1题创建的触发器，实现当向借阅表插入一条记录时，检查该记录的借书证号在读者表中是否存在，检查图书的ISBN在BOOK表中是否存在，以及图书的库存量是否大于0,若有一项为否，则不允许插入，如果条件满足，则向借阅表中插入一条记录后，修改读者表中借书量增加1，图书表中该书的库存量减1。

drop trigger table_insert
alter trigger Lend_insert
on Lend
after insert
as
declare@借书证号char(12),@ISBN编号char(16)
select@借书证号=借书证号from inserted
select@isbn编号=(select@ISBN编号from reader,lend where@借书证号
if(@借书证号not in(select*from reader.借书证号)
or@isbn编号not in(select*from book.isbn编号)
or exists(select*from book where库存量<=0))
begin
print'不允许插入'
end
else
begin
update reader set借阅数量=借阅数量+1
where@借书证号in
(
select借书证号
from redaer
)
update book set库存量=库存量+1
where@isbn编号in
(select isbn编号from reader)
End
8.利用sp_helptext查看第2题创建的触发器，然后将其修改为加密存储过程（with encryption），再查看其存储过程的文本信息。

alter trigger delete_Reader
on Reader with encryption after delete
as
declare@借书证号char(12),@ISBN编号char(16)
select@借书证号=借书证号from inserted
select@isbn编号=(select@ISBN编号from reader,lend where@借书证号
if(@借书证号not in(select*from reader.借书证号)
or@isbn编号not in(select*from book.isbn编号)
or exists(select*from book where库存量<=0))
begin
print'不允许插入'
end
9.利用lend表和book表创建视图view_lend，视图属性有借书证号、isbn、书名、出版社、条码号、借阅日期等。

再创建一个存储过程，当向view_lend插入借阅记录时，转化为分别向lend表和book表插入记录。

提示：触发器的要求任选其一完成：吧
(1)只是用于对book中不存在的图书借阅操作
create view view_lend
as
select借书证号,lend.isbn号,书名,出版社,ISBN编号,借阅日期
from Lend,Book
where Lend.ISBN号=Book.ISBN编号
create trigger view_lend_book
on view_lend instead of insert
as
begin
declare@借书证号char(12),@ISBN编号char(16),@书名char(50),@出版社varchar(50),@ISBN号char(10),@借阅日期smalldatetime
select@借书证号=借书证号,@ISBN编号=ISBN编号,@书名=书名,@出版社=出版社,@ISBN 号=ISBN号,@借阅日期=借阅日期
from inserted
insert into Book(ISBN编号,书名,出版社)
values(@ISBN编号,@书名,@出版社)
insert into Lend(借书证号,ISBN号,借阅日期)
values(@借书证号,@ISBN号,@借阅日期)
end
(2)先检查book中是否存在当前被借阅图书的信息，若存在，图书数量减1，只想lend 表插入借阅信息；若该图书在book中不存在，则分别向book表和lend表分别插入相应记
录。