资源描述：
《数据库实验9答案.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、(1)设置一个触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据，否则出错。createtriggerEmploteeDeleteonemployeefordeleteasifexists(select*fromdeleted)beginifuser!='dbo'rollbackend(2)在OrderMaster表中创建触发器，插入数据时要先检查Employee表中是否存在同样值的业务员编号，如果不存在则不允许插入。createtriggerinsOrderMasteronOrderM
2、asterforinsertasbeginifexists(select*frominsertedwheresalerNonotin(selectemployeeNofromEmployee))rollback;end(3)级联更新：当更新Customer表中customerNo列的值时，同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。createtriggeruptCustomeronCustomerforupdateasdeclare@oldCustomerNoc
3、har(9),@newCustomerNochar(9)if(selectcount(*)frominserted)>1rollbackelsebeginifupdate(customerNo)beginselect@oldCustomerNo=customerNofromdeletedselect@newCustomerNo=customerNofrominsertedupdateOrderMastersetcustomerNo=@newCustomerNowherecustomerNo=@oldCus
4、tomerNoendend(4)对Product表写一个UPDATE触发器。当更新“主板”类的商品时，其单价不得高于原价格的10%createtriggeruptProductonProductforupdateasbegindeclare@oldPricenumeric(7,2),@newPricenumeric(7,2)ifupdate(productPrice)begindeclaremyCurcursorforselecta.productPrice,b.productPricefrominser
5、teda,deletedbwherea.productNo=b.productNoandb.productClass='主板'openmyCurfetchmyCurinto@newPrice,@oldPricewhile(@@fetch_status=0)beginif@newPrice>@oldPrice+@oldPrice*0.1rollbackfetchmyCurinto@newPrice,@oldPriceendclosemyCurdeallocatemyCurendend(5)保证商品的销售单价
6、必须在成本价的正负10%之内插入触发器createtriggerinsOrderDetailonOrderDetailforinsertasbegindeclare@productNochar(9),@pricenumeric(7,2)declare@productPricenumeric(7,2),@orderNochar(12)declaremyCurcursorforselectorderNo,b.productNo,price,productPricefromProducta,insertedbw
7、herea.productNo=b.productNoopenmyCurfetchmyCurinto@orderNo,@productNo,@price,@productPricewhile(@@fetch_status=0)beginif(@price>@productPrice+@productPrice*0.1or@price<@productPrice-@productPrice*0.1)deletefromOrderDetailwhereorderNo=@orderNoandproductNo=
8、@productNofetchmyCurinto@orderNo,@productNo,@price,@productPriceendclosemyCurdeallocatemyCurend更新触发器createtriggeruptOrderDetailonOrderDetailforupdateasbegindeclare@productNochar(9),@oldPricenumeric(7,2)declare@newPr
