sql实验报告.doc

实验四触发器实验after触发器在lineitem表上定义一个after触发器,当修改列项目extendedpricediscounttax时,要把orders表的totalprice一起修改,以保证数据一致性CREATETRIGGERtrig_lineitem_price_updateonlineitemforupdateasbeginif(UPDATE(extendedprice)orUPDATE(tax)orUPDATE(discount))begin --声明游标变量指向inserted表 declarecursor_insertedcursorread_only forselectorderkey,linenumber,extendedprice,discount,taxfrominserted --声明变量获取查找信息 declareorderkeyint,linenumberint,extendedpricereal,discountreal,taxreal --打开游标 opencursor_inserted --读取游标 fetchnextfromcursor_insertedintoorderkey,linenumber,extendedprice,discount,tax whileFETCH_STATUS=0 begin --声明一个变量保存重新计算的新价格 declarenew_totalpricereal selectnew_totalprice=extendedprice*(1-discount)*(1+tax) --用新的总价格变量更新orders表的totalprice updateorderssettotalprice=new_totalpricewhereorderkey=orderkey fetchnextfromcursor_insertedintoorderkey,linenumber,extendedprice,discount,tax end deallocatecursor_insertedendend (2)在lineitem表上定义一个after触发器,当增加一项订单明细时,自动修改orders表的totalprice,以保证数据一致性CREATETRIGGERtrig_lineitem_price_insertonlineitemforinsertasbegin --声明游标变量指向inserted表 declarecursor_insertedcursorread_only forselectorderkey,linenumber,extendedprice,discount,taxfrominserted --声明变量获取查找信息 declareorderkeyint,linenumberint,extendedpricereal,discountreal,taxreal --打开游标 opencursor_inserted --读取游标 fetchnextfromcursor_insertedintoorderkey,linenumber,extendedprice,discount,tax whileFETCH_STATUS=0 begin --声明一个变量保存重新计算的新价格 declarenew_totalpricereal selectnew_totalprice=extendedprice*(1-discount)*(1+tax) --用新的总价格变量更新orders表的totalprice updateorderssettotalprice=totalprice+new_totalpricewhereorderkey=orderkey fetchnextfromcursor_insertedintoorderkey,linenumber,extendedprice,discount,tax end deallocatecursor_insertedend(3)在lineitem表上定义一个after触发器,当删除一项订单明细记录时,自动修改orders表的totalprice,以保证数据一致性CREATETRIGGERtrig_lineitem_price_deleteonlineitemfordeleteASbegin --声明游标变量指向deleted表 declarecursor_deletedcursorread_only forselectorderkey,linenumber,extendedprice,discount,taxfromdeleted --声明变量获取查找信息 declareorderkeyint,linenumberint,