SQL实验报告.doc

实验四触发器实验after触发器在lineitem表上定义一个after触发器,当修改列项目extendedpricediscounttax时,要把orders表得totalprice一起修改,以保证数据一致性CREATETRIGGER trig_lineitem_price_updateonlineitemforupdateas beginif(UPDATE(extendedprice) orUPDATE(tax) orUPDATE(discount))begin -—声明游标变量指向inserted表 declare cursor_insertedcursorread_only forselectorderkey,linenumber,extendedprice,discount,tax from insertedﻩ-—声明变量获取查找信息 declareorderkeyint,***@linenumber int,extendedprice real,discountreal,taxreal —-打开游标ﻩopencursor_insertedﻩ-—读取游标 fetch next fromcursor_******@orderkey,***@linenumber,extendedprice,***@discount,tax whileFETCH_STATUS=0ﻩbegin ﻩ —-声明一个变量保存重新计算得新价格ﻩﻩ******@new_totalprice realﻩ ******@new_totalprice=***@extendedprice*(1-***@discount)*(1+***@tax) —-用新得总价格变量更新orders表得totalpriceﻩﻩupdateorders settotalprice=new_totalpricewhereorderkey=orderkey ﻩfetchnextfromcursor_******@orderkey,***@linenumber,***@extendedprice,discount,***@taxﻩendﻩdeallocatecursor_insertedendendﻩ(2)在lineitem表上定义一个after触发器,当增加一项订单明细时,自动修改orders表得totalprice,以保证数据一致性CREATETRIGGERtrig_lineitem_price_insertonlineitemforinsert asbeginﻩ——声明游标变量指向inserted表 declare cursor_inserted cursorread_onlyﻩ forselectorderkey,linenumber,extendedprice,discount,tax frominserted --声明变量获取查找信息ﻩ******@orderkey int,***@linenumberint,extendedprice real,***@discount real,***@taxrealﻩ—-打开游标 opencursor_insertedﻩ--读取游标 fetch next from cursor_inserted into ***@orderkey,linenumber,extendedprice,discount,taxﻩwhile@***@FETCH_STATUS=0ﻩbeginﻩﻩ—-声明一个变量保存重新计算得新价格 ﻩ******@new_totalpricereal ﻩselectnew_totalprice=***@extendedprice*(1-discount)*(1+tax)ﻩﻩ-—用新得总价格变量更新orders表得totalpriceﻩ update ordersset totalprice=totalprice+***@new_totalpricewhereorderkey=orderkey ﻩfetchnextfromcursor_insertedintoorderkey,***@linenumber,extendedprice,***@discount,***@tax endﻩdeallocatecursor_insertedend(3) 在lineitem表上定义一个after触发器,当删除一项订单明细记录时,自动修改orders表得totalprice,以保证数据一致性CREATETRIGGERtrig_lineitem_price_delete onlineitemfordelete ASbegin --声明游标变量指向deleted表 declarecursor_deletedcursorread_onlyﻩﻩfor selectorderkey,linenumber,extendedprice