数据库系统开发实验

数据库系统开发触发器设计与班级（..e修改信息记录表Production.ProductUpdaog的内容：记录编号、订单编号、订单明细编号、产品编设计触发器测试方案并给出测试令和结果，必要时可对进行分析useuse[AdventureWorks]ifOBJECT_ID('[Production].[UpdateProductLog]','U'isnotnull--如果已经存在则删除droptable[Production].[UpdateProductLog]createtable[Production].[UpdateProductLog按要求设计的表UpdateProductIDintprimarykey,SalesOrderIDintnotnull,SalesOrderDetailIDintnotnull,ProductIDintnotnull,ProductListPricemoneynotnull,ProductHistoryUnitPricemoneynotnull,ProductNowUnitPricemoneynotnull,LoginNamechar(20)notnull)2过use[AdventureWorks]ifobject_idProduction].[recUpdateProductLog]','Pisnotnull--如果存在则删除dropprocedure[Production].[recUpdateProductLog]createprocedure[Production].[recUpdateProductLog]@SalesOrderIDint,@SalesOrderDetailIDint,@ProductIDint,@ProductListPricemoney报价,--@ProductNowUnitPricemoney当前单价withexecuteascallersetnocountonbegintrybegindeclare@maxidint;表示表中最大的ifnotexistsselect1from[Production].[UpdateProductLog查看是否为空表set@maxid=select@maxid=max(UpdateProductID)fromdeclare@hostnamechar(20);--当前修改表的用户的登录名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判断是否当前价格大于报价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（超过了报价）查询分析：新修改的产品单价大于了报价，所以执行了raiserror(N'un