1.2.1 内容
检查订单明细表Sales.SalesOrderDetail中的信息,如果修改记录中的产品单价UnitPrice大于产品公开报价(Production.Product.ListPrice),则不能进行修改并抛出错误信息,否则,进行修改并将修改的有关信息写到Production.ProuctUpdateLog表中。
1.2.2 要求
1. 使用RAISEERROR抛出错误信息。
2. 修改信息记录表Production.ProductUpdateLog的内容:记录编号、订单编号、订单明细编号、产品编号、产品的公开报价、修改前产品的单价、修改后产品的单价、修改者的登录名。使用存储过程完成该功能,并在存储过程中调用该存储过程。
3. 给出触发器和存储过程的源代码和简要的说明(可以在代码中使用注释进行说明)。
4. 设计触发器测试方案并给出测试的命令和结果,必要时可对测试结果进行分析。
实验内容:
首先,用Windows系统下的登录,附加数据库AdventureWorks
按照实验内容,我们先来查询一下AdventureWorks中的订单明细表Sales.SalesOrderDetail。
语句:
USE AdventureWorks
GO
SELECT * FROM Sales.SalesOrderDetail
GO
查询结果如下:
根据实验内容,创建名为Production.ProuctUpdateLog(产品更新日志)的表。其属性分别为记录编号,订单编号,订单明细编号,产品编号,产品公开报价,修改前产品的单价,修改后产品单价,修改者登录名。
语句:
/*记录编号,订单编号,订单明细编号,产品编号,产品公开报价,修改前产品的单价
,修改后产品单价,修改者登录名*/
USE AdventureWorks
GO
CREATE TABLE Production.ProductUpdateLog
(
记录编号 int IDENTITY primary key,
订单编号 int not null,
订单明细编号 int not null,
产品编号 int not null,
产品公开报价 money,
修改前产品单价 money,
修改后产品单价 money,
修改者登录名 nvarchar(50) not null
)
GO
运行结果如下:
将修改者登录名设为不准为空,同时用IDENTITY关键字设主键“记录编号”为自动增长。
表格Production.ProductUpdateLog创建成功,之后查询以检验其创建成功。
题目要求,当更新产品价格满足不大于公开报价条件时,更新表格Sales.SalesOrderDetail,并且向表Production.ProductUpdateLog中插入一条记录。要求此功能用存储过程实现。
下面创建一个名为Production.Record_Update的存储过程,以完成向表Production.ProductUpdateLog中插入记录的功能。
语句:
USE AdventureWorks
GO
IF OBJECT_ID('Production.Record_Update_Price','P')IS NOT NULL
DROP PROCEDURE Production.Record_Update_Price
GO
--如果数据库中存在名称为Production.Record_Update_Price的存储过程
--则删除该存储过程
--创建存储过程Production.Record_Update_Price,它有个参数,
--其中@SalesorderID 表示订单编号,@SalesorderdetailID 表示订单明细编号
--@ProductID 表示产品编号,@PublicPrice 表示公开报价
--@PrePrice 表示修改前价格,@PostPrice 表示修改后报价
--@Operator 表示修改者登录名
CREATE PROCEDURE Production.Record_Update_Price
--@RecordID int,
--因为表格ProductUpdateLog的主键设为IDENTITY性质,不用传参
@SalesorderID int,
@SalesorderdetailID int, @ProductID int,
@PublicPrice money, @PrePrice money,
@PostPrice money, @Operator nvarchar(50)
AS
--向表ProductUpdateLog插入一条记录,参数纷纷对应
INSERT INTO Production.ProductUpdateLog
(
--记录编号,
订单编号,
订单明细编号, 产品编号,
产品公开报价, 修改前产品单价,
修改后产品单价, 修改者登录名
)
VALUES
(
--@RecordID,
@SalesorderID,
@SalesorderdetailID, @ProductID,
@PublicPrice, @PrePrice,
@PostPrice, @Operator
)
GO
点击执行,刷新左栏的对象资源管理器,打开数据库-AdventureWorks-可编程性-存储过程则可见如图所示:
下面来对触发器进行分析。题目中要求,当更新产品价格的时候触发触发器,判断如果所更新的产品价格大于产品公开报价的话就调用RAISERROR报错提示错误信息,否则,则进行修改并且将修改的相关信息保存到表Production.ProductUpdateLog中。
创建一个名为Sales.Price_Update的触发器,Sales为其架构名称。
代码大致思路为:首先判断所创建的触发器是否存在同名触发器,若存在则将其删除。接下来在表Sales.SalesOrderDetail中创建名为Sales.Price_Update的instead of触发器。进入触发器内容,如果所更新的产品单价比产品公开报价大则不执行更新,RAISERROR报错为用户显示错误信息。如果满足产品单价不大于公开报价的条件,则对Sales.SalesOrderDetail表相对应记录进行更新操作,并且在表Production.ProductUpdateLog中插入更新信息。
语句:
USE AdventureWorks
GO
--如果已经存在名为Sales.Price_Update的触发器,则删除它
IF OBJECT_ID('Sales.Price_Update','TR')IS NOT NULL
DROP TRIGGER Sales.Price_Update
GO
--在表Sales.SalesOrderDetail的Update操作上创建
--Instead of触发器Sales.Price_Update
CREATE TRIGGER Sales.Price_Update
ON Sales.SalesOrderDetail
INSTEAD OF Update
AS
--当更新插入记录的更新价格UnitPrice大于
--产品的公开报价Production.Product.ListPrice时
--调用RAISERROR报错,进行操作回滚
IF(EXISTS( SELECT I.UnitPrice
FROM Production.Product P, inserted I
WHERE I.UnitPrice > P.ListPrice AND P.ProductID = I.ProductID))
BEGIN
RAISERROR('修改的产品单价不能大于产品的公开报价!', 10, 1)
ROLLBACK TRANSACTION
END
--如果符合更新价格不大于公开报价的条件
--则调用存储过程Production.Record_Update_Price
ELSE
BEGIN
--声明相对应的个参数,数据类型一致对应
DECLARE @SalesorderID int,
@SalesorderdetailID int,
@ProductID int,
@ListPrice money,
@PreUnitPrice money,
@PostUnitPrice money,
@Operator nvarchar(50)
--订单编号、订单明细编号、产品编号及产品修改后价格
--皆取自表inserted相对应值
SELECT @SalesorderID = SalesOrderID,
@SalesorderdetailID = SalesOrderDetailID,
@ProductID = ProductID,
@PostUnitPrice = UnitPrice
FROM inserted
--产品公开报价取自表Production.Product的ListPrice
SELECT @ListPrice = ListPrice
FROM Production.Product P
WHERE P.ProductID = (
SELECT ProductID
FROM inserted
)
--执行更新操作,将表Sales.SalesOrderDetail所对应的记录的
--UnitPrice值更新
UPDATE Sales.SalesOrderDetail
SET UnitPrice = @PostUnitPrice
WHERE Sales.SalesOrderDetail.SalesOrderID = @SalesorderID
AND Sales.SalesOrderDetail.SalesOrderDetailID = @SalesorderdetailID
--修改前的产品价格取自表中deleted
--表Sales.SalesOrderDetail对应被删除的记录UnitPrice
SELECT @PreUnitPrice = UnitPrice
FROM deleted D
WHERE D.ProductID =(
SELECT ProductID
FROM inserted
)
--获取当前修改者登录名
--在网上搜到了这个系统内置函数
SELECT @Operator = SYSTEM_USER
--将个参数对应位置传入存储过程Production.Record_Update_Price
EXECUTE Production.Record_Update_Price @SalesorderID,
@SalesorderdetailID,
@ProductID, @ListPrice,
@PreUnitPrice, @PostUnitPrice,
@Operator
--提示已经进入存储过程
PRINT 'HERE COMES A PROCEDURE...'
END
GO
执行以上代码,触发器创建成功,得到结果如下图:
下面对触发器及存储过程进行测试。
先对没有进行任何更新操作的Sales.SalesOrderDetail进行查询操作。结果如下图:
对表中订单明细编号为1的记录行操作。
首先查询一下与订单明细编号为1的产品编号一致的产品公开报价的数值。如下图所示,ListPrice为3374.99。
首先测试存储过程Production.Record_Update_Price,故我将该产品的更新价格定为3000低于ListPrice,满足更新价格低于公开报价这一条件。
如下为代码与执行结果:
对表Sales.SalesOrderDetail再一次进行查询,得到了更新后的结果订单明细编号为1的UnitPrice改为了3000,说明更新成功。那么相应的表Production.ProductUpdateLog也应该多了一条更新记录。
对于情况更新操作成功测试完毕,下面看一下更新操作失败的情况。将更新价格UnitPrice改为4000,再次进行执行,结果如下图:
RAISERROR将之前预置错误消息输出,下面还有相应的错误提示。说明触发器已经使得进程终止。
总结:
通过实验二,掌握如何使用RAISEERROR抛出错误信息,以及使用存储过程完成功能,并在存储过程中调用该存储过程。设计触发器测试方案并给出测试的命令和结果,必要时可对测试结果进行分析。
思考题:
根据实验指导书中的“INSTEAD OF触发器示例二”的内容回答下面问题。
◆ INSTEAD OF触发器需要撤消触发事件的操作吗?
答:不需要,INSTEAD OF触发器只是替代操作。
◆ 触发器中的撤消事务的操作ROLLBACK TRANSACTION是撤消delete操作吗?如果不是,是撤消什么样的操作?举例说明。
答:不是,是撤销的TYR和CATCH操作。
本文来源:https://www.2haoxitong.net/k/doc/d5cee60cf042336c1eb91a37f111f18582d00c37.html
文档为doc格式