SQL实验报告.pdf

该【SQL实验报告 】是由【mama1】上传分享，文档一共【20】页，该文档可以免费在线阅读，需要了解更多关于【SQL实验报告 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。 : .
实验四触发器实验
（一） after 触发器
（1） 在 lineitem表上定义一个 after 触发器，当修改列项目 extendedprice discount
tax 时，要把 orders 表的 totalprice一起修改，以保证数据一致性
CREATE TRIGGER trig_lineitem_price_update on lineitem
for update
as
begin
if (UPDATE(extendedprice) or UPDATE(tax) or UPDATE(discount))
begin
--声明游标变量指向 inserted 表
declare cursor_inserted cursor read_only
for select orderkey,linenumber,extendedprice,discount,tax from inserted
-- 声明变量获取查找信息
declare ***@orderkey int,***@linenumber int,***@extendedprice real,***@discount real,***@tax
real
-- 打开游标
open cursor_inserted
-- 读取游标
fetch next from cursor_inserted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
while @***@FETCH_STATUS=0
begin
--声明一个变量保存重新计算的新价格 : .
declare ***@new_totalprice real
select ***@new_totalprice=***@extendedprice*(1-***@discount)*(1+***@tax)
--用新的总价格变量更新orders表的 totalprice
update orders set totalprice=***@new_totalprice where orderkey=***@orderkey

fetch next from cursor_inserted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
end
deallocate cursor_inserted
end
end

（2）在 lineitem 表上定义一个 after 触发器，当增加一项订单明细时，
自动修改 orders 表的 totalprice，以保证数据一致性
CREATE TRIGGER trig_lineitem_price_insert on lineitem
for insert
as
begin
--声明游标变量指向inserted 表
declare cursor_inserted cursor read_only
for select orderkey,linenumber,extendedprice,discount,tax from inserted
-- 声明变量获取查找信息
declare ***@orderkey int,***@linenumber int,***@extendedprice real,***@discount real,***@tax
real
-- 打开游标
open cursor_inserted : .
-- 读取游标
fetch next from cursor_inserted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
while @***@FETCH_STATUS=0
begin
--声明一个变量保存重新计算的新价格
declare ***@new_totalprice real
select ***@new_totalprice=***@extendedprice*(1-***@discount)*(1+***@tax)
--用新的总价格变量更新orders 表的totalprice
update orders set totalprice=totalprice+***@new_totalprice where
orderkey=***@orderkey
fetch next from cursor_inserted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
end
deallocate cursor_inserted
end

(3) 在lineitem表上定义一个 after 触发器，当删除一项订单明细记录时，
自动修改orders表的totalprice ，以保证数据一致性
CREATE TRIGGER trig_lineitem_price_delete on lineitem
for delete
AS
begin
--声明游标变量指向deleted 表
declare cursor_deleted cursor read_only
for select orderkey,linenumber,extendedprice,discount,tax from deleted : .
-- 声明变量获取查找信息
declare ***@orderkey int,***@linenumber int,***@extendedprice real,***@discount real,***@tax
real
-- 打开游标
open cursor_deleted
-- 读取游标
fetch next from cursor_deleted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
while @***@FETCH_STATUS=0
begin
--声明一个变量保存重新计算的新价格
declare ***@new_totalprice real
select ***@new_totalprice=***@extendedprice*(1-***@discount)*(1+***@tax)
--用新的总价格变量更新orders 表的totalprice
update orders set totalprice=totalprice-***@new_totalprice where
orderkey=***@orderkey
fetch next from cursor_inserted into
***@orderkey,***@linenumber,***@extendedprice,***@discount,***@tax
end
deallocate cursor_inserted
end

(4)验证 update 触发器
--查看号订单的 totalprice
select * from orders where orderkey=1830; : .


--查看明细表的相关信息
select * from lineitem where orderkey=1830 and linenumber=1;

--验证 update 触发器
update lineitem set tax=tax+ where orderkey=1830;
: .

（二） instead of 触发器
（1） 在 lineitem 表上定义一个instead of update触发器，当修改明细表中的数量
quantity时，应先检查供应表 partsupp 的 availqty是否足够，不足够则拒绝执行，
否则执行并修改相应数值以保证数据一致性
由于 instead of 触发器更新某个表会使得该表上其他不满足更新列不能更新，因
此逆向思维使用 after 触发器实现相同效果 即先更新 quantity ，再比较 availqty ，
如果满足更新数量，就修改 partsupp表的 availqty，如果不满足，则把 lineitem
的 quantity更新之后的数据重新修改回来
create trigger trig_lineitem_quantity_update on lineitem
for update
as
begin
if UPDATE(quantity)
begin
--声明游标变量分别指向inserted表和 deleted表
declare cursor_inserted cursor read_only for
select orderkey,partkey,suppkey,linenumber,quantity from inserted
declare cursor_deleted cursor read_only for select quantity from deleted
-- 声明变量获取查找信息
declare ***@quantity_diff_lineitem int,***@quantity_partsupp int
declare ***@suppkey int,***@partkey int,***@orderkey int, : .
***@linenumber int ,***@qty_inserted int ,***@qty_deleted int
-- 打开游标
open cursor_inserted
open cursor_deleted
-- 读取游标数值赋给变量
fetch next from cursor_inserted
into ***@orderkey,***@partkey,***@suppkey,***@linenumber,***@qty_inserted
fetch next from cursor_deleted into ***@qty_deleted
while @***@fetch_status=0
begin
--计算订单明细修改时，订购数量的变化值 inserted 表项-deleted表项
select ***@quantity_diff_lineitem=***@qty_inserted-***@qty_deleted
--从 partsupp表获取 availqty 值，注意 partsupp 表的主键为（partkey ，suppkey ）
select ***@quantity_partsupp=availqty from partsupp
where suppkey=***@suppkey and partkey=***@partkey
-- 开始判断
begin
if ***@quantity_diff_lineitem=0