數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)實(shí)驗(yàn)

數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)觸發(fā)器設(shè)計(jì)與班級(jí)（..e修改信息記錄表Production.ProductUpdaog的內(nèi)容：記錄編號(hào)、訂單編號(hào)、訂單明細(xì)編號(hào)、產(chǎn)品編設(shè)計(jì)觸發(fā)器測(cè)試方案并給出測(cè)試令和結(jié)果，必要時(shí)可對(duì)進(jìn)行分析useuse[AdventureWorks]ifOBJECT_ID('[Production].[UpdateProductLog]','U'isnotnull--如果已經(jīng)存在則刪除droptable[Production].[UpdateProductLog]createtable[Production].[UpdateProductLog按要求設(shè)計(jì)的表UpdateProductIDintprimarykey,SalesOrderIDintnotnull,SalesOrderDetailIDintnotnull,ProductIDintnotnull,ProductListPricemoneynotnull,ProductHistoryUnitPricemoneynotnull,ProductNowUnitPricemoneynotnull,LoginNamechar(20)notnull)2過(guò)use[AdventureWorks]ifobject_idProduction].[recUpdateProductLog]','Pisnotnull--如果存在則刪除dropprocedure[Production].[recUpdateProductLog]createprocedure[Production].[recUpdateProductLog]@SalesOrderIDint,@SalesOrderDetailIDint,@ProductIDint,@ProductListPricemoney報(bào)價(jià),--@ProductNowUnitPricemoney當(dāng)前單價(jià)withexecuteascallersetnocountonbegintrybegindeclare@maxidint;表示表中最大的ifnotexistsselect1from[Production].[UpdateProductLog查看是否為空表set@maxid=select@maxid=max(UpdateProductID)fromdeclare@hostnamechar(20);--當(dāng)前修改表的用戶的登錄名select@hostnamehostnamefrommaster.sys.sysprocessesinsertinto[Production].[UpdateProductLog]values(@maxid+1,)commitendtrybeginendtrybegincatchraiserror(N'in[Production].[recUpdateProductLog]error',11,1);if@@TRANCOUNT>0rollback3ifobject_id('Sales.SalesOrderTrigger','TR')isnotnull droptriggerSales.SalesOrderTriggercreatetriggerSales.SalesOrderTriggeronSales.SalesOrderDetailafterupdateasdeclare@priceselect@price=UnitPricefrominserted;declare@ProductIDint;select@ProductID=ProductIDfrominserted;declare@listpricemoney;select@listprice=ListPricefromProduction.ProductwhereProductID=@ProductID;if@price@listprice判斷是否當(dāng)前價(jià)格大于報(bào)價(jià)raiserror(N'unitpricecannotbehigherthanlistprice!',11,1);if@@TRANCOUNT>0rollbackdeclare@SalesOrderID1select@SalesOrderID1=SalesOrderIDfrominserted;declare@SalesOrderDetailsIDint;select@SalesOrderDetailsID=SalesOrderDetailIDfromdeclare@ProductHistoryUnitPriceselect@ProductHistoryUnitPrice=UnitPricefromdeleted;declare@ProductNowUnitPricemoney;select@ProductNowUnitPrice=UnitPricefrominserted;declare@statusint;exec@status=Production.recUpdateProductLog@SalesOrderID=@SalesOrderID1,@SalesOrderDetailID=@SalesOrderDetailsID,@ProductID=@ProductID,@ProductListPrice=@listprice,@ProductHistoryUnitPrice=@ProductHistoryUnitPrice,@ProductNowUnitPrice=@ProductNowUnitPrice1000Sales.SalesOrderDetailProductID777ID7775000（超過(guò)了報(bào)價(jià)）查詢分析：新修改的產(chǎn)品單價(jià)大于了報(bào)價(jià)，所以執(zhí)行了raiserror(N'un