sql实验报告.doc

-
. z.
实验四触发器实验
after 触发器
在lineitem表上定义一个after触发器，当修改列工程e*tendedprice discount
ta*时，要把oend
(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,e*tendedprice,discount,ta* from deleted
-- 声明变量获取查找信息
declare orderkey int,linenumber int,e*tendedprice real,discount real,ta* real
-- 翻开游标
open cursor_deleted
-- 读取游标
fetch ne*t from cursor_deleted into orderkey,linenumber,e*tendedprice,discount,ta*
while FETCH_STATUS=0
begin
--声明一个变量保存重新计算的新价格
declare new_totalprice real
select new_totalprice=e*tendedprice*(1-discount)*(1+ta*)
--用新的总价格变量更新orders表的totalprice
update orders set totalprice=totalprice-new_totalprice where orderkey=orderkey
-
. z.
fetch ne*t from cursor_inserted into orderkey,linenumber,e*tendedprice,discount,ta*
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 ta*=ta*+ where orderkey=1830;
instead of 触发器
在lineitem表上定义一个instead of update触发器，当修改明细表中的数量quantity时，应先检查供给表partsupp的availqty是否足够，缺乏够则