SQL实验报告

实验四触发器实验（一）after触发器（1）在lineitem表上定义一个after触发器，当修改列项目extendedpricediscounttax时，要把orders表的totalprice一起修改，以保证数据一致性CREATETRIGGERtrig_lineitem_price_updateonlineitemforupdateasbeginif(UPDATE(extendedprice)orUPDATE(tax)orUPDATE(discount))begin--声明游标变量指向inserted表declarecursor_insertedcursorread_onlyforselectorderkey,linenumber,extendedprice,discount,taxfrominserted--声明变量获取查找信息declare@orderkeyint,@linenumberint,@extendedpricereal,@discountreal,@taxreal--打开游标opencursor_inserted--读取游标fetchnextfromcursor_insertedinto@orderkey,@linenumber,@extendedprice,@discount,@taxwhile@@FETCH_STATUS=0begin--声明一个变量保存重新计算的新价格declare@new_totalpricerealselect@new_totalprice=@extendedprice*(1-@discount)*(1+@tax)--用新的总价格变量更新orders表的totalpriceupdateorderssettotalprice=@new_totalpricewhereorderkey=@orderkeyfetchnextfromcursor_insertedinto@orderkey,@linenumber,@extendedprice,@discount,@taxenddeallocatecursor_insertedendend（2）在lineitem表上定义一个after触发器，当增加一项订单明细时，自动修改orders表的totalprice，以保证数据一致性CREATETRIGGERtrig_lineitem_price_insertonlineitemforinsertasbegin--声明游标变量指向inserted表declarecursor_insertedcursorread_onlyforselectorderkey,linenumber,extendedprice,discount,taxfrominserted--声明变量获取查找信息declare@orderkeyint,@linenumberint,@extendedpricereal,@discountreal,@taxreal--打开游标opencursor_inserted--读取游标fetchnextfromcursor_insertedinto@orderkey,@linenumber,@extendedprice,@discount,@taxwhile@@FETCH_STATUS=0begin--声明一个变量保存重新计算的新价格declare@new_totalpricerealselect@new_totalprice=@extendedprice*(1-@discount)*(1+@tax)--用新的总价格变量更新orders表的totalpriceupdateorderssettotalprice=totalprice+@new_totalpricewhereorderkey=@orderkeyfetchnextfromcursor_insertedinto@orderkey,@linenumber,@extendedprice,@discount,@taxenddeallocatecursor_insertedend(3)在lineitem表上定义一个after触发器，当删除一项订单明细记录时，自动修改orders表的totalprice，以保证数据一致性CREATETRIGGERtrig_lineitem_price_deleteonlineitemfordeleteASbegin--声明游标变量指向deleted表declarecursor_deletedcursorread_onlyforselectorderkey,linenumber,extendedprice,discount,taxfromdeleted--声明变量获取查找信息declare@orderkeyint,@linenumberint,@extendedpricereal,@discountreal,@taxreal--打开游标opencursor_deleted--读取游标fetchnextfromcursor_deletedinto@orderkey,@linenumber,@extendedprice,@discount,@taxwhile@@FETCH_STATUS=0begin--声明一个变量保存重新计算的新价格declare@new_totalpricerealselect@new_totalprice=@extendedprice*(1-@discount)*(1+@tax)--用新的总价格变量更新orders表的totalpriceupdateorderssettotalprice=totalprice-@new_totalpricewhereorderkey=@orderkeyfetchnextfromcursor_insertedinto@orderkey,@linenumber,@extendedprice,@discount,@taxenddeallocatecursor_insertedend(4)验证update触发器--查看号订单的totalpriceselect*fromorderswhereorderkey=1830;--查看明细表的相关信息select*fromlineitemwhereorderkey=1830andlinenumber=1;--验证update触发器updatelineitemsettax=tax+0.05whereorderkey=1830;（二）insteadof触发器（1）在lineitem表上定义一个insteadofupdate触发器，当修改明细表中的数量quantity时，应先检查供应表partsupp的availqty是否足够，不足够则拒绝执行，否则执行并修改相应数值以保证数据一致性由于insteadof触发器更新某个表会使得该表上其他不满足更新列不能更新，因此逆向思维使用after触发器实现相同效果即先更新quantity，再比较availqty，如果满足更新数量，就修改partsupp表的availqty，如果不满足，则把lineitem的quantity更新之后的数据重新修改回来createtriggertrig_lineitem_quantity_updateonlineitemforupdateasbeginifUPDATE(quantity)begin--声明游标变量分别指向inserted表和deleted表declarecursor_insertedcursorread_onlyforselectorderkey,partkey,suppkey,linenumber,quantityfrominserteddeclarecursor_deletedcursorread_onlyforselectquantityfromdeleted--声明变量获取查找信息declare@quantity_diff_lineitemint,@quantity_partsuppintdeclare@suppkeyint,@partkeyint,@orderkeyint,@linenumberint,@qty_insertedint,@qty_deletedint--打开游标opencursor_insertedopencursor_deleted--读取游标数值赋给变量fetchnextfromcursor_insertedinto@orderkey,@partkey,@suppkey,@linenumber,@qty_insertedfetchnextfromcursor_deletedinto@qty_deletedwhile@@fetch_status=0begin--计算订单明细修改时，订购数量的变化值inserted表项-deleted表项select@quantity_diff_lineitem=@qty_inserted-@qty_deleted--从partsupp表获取availqty值，注意partsupp表的主键为（partkey，suppkey）select@quantity_partsupp=availqtyfrompartsuppwheresuppkey=@suppkeyandpartkey=@partkey--开始判断beginif@quantity_diff_lineitem=0print'更新的数量和原表中的值相同，不需要更新'elseif@quantity_diff_lineitem=@quantity_partsuppbeginupdatepartsuppsetavailqty=availqty-@quantity_diff_lineitemwheresuppkey=@suppkeyandpartkey=@partkeyprint'两个表都更新成功'endelsebeginupdatelineitemsetquantity=quantity+@quantity_diff_lineitemwhereorderkey=@orderkeyandlinenumber=@linenumberprint'更新失败'endendfetchnextfromcursor_insertedinto@orderkey,@partkey,@suppkey,@linenumber,@qty_insertedfetchnextfromcursor_deletedinto@qty_deletedenddeallocatecursor_inserteddeallocatecursor_deletedendend（2）在lineitem表上定义一个insteadofinsert触发器，当插入明细表中一条记录时，应先检查供应表partsupp的availqty是否足够quantity的数量createtriggertrig_lineitem_quantity_insertonlineiteminsteadofinsertasbegin--声明游标变量指向inserted表declarecursor_insertedcursorread_onlyforselectorderkey,partkey,suppkey,linenumber,quantityfrominserted--声明变量获取查找信息declare@quantityint,@availqtyint,@suppkeyint,@partkeyint,@orderkeyint,@linenumberint--打开游标opencursor_inserted--读取游标fetchnextfromcursor_insertedinto@orderkey,@partkey,@suppkey,@linenumber,@quantitywhile@@FETCH_STATUS=0begin--为变量赋值select@availqty=availqtyfrompartsuppwheres