使用MS SQL 2005 INSTEAD-OF触发器
触发器是类似于存储程序的数据库对象，它响应数据库环境下的某个请求。SQL Sever 2005包含3个触发器对象:AFTER，数据定义语言 (DDL)和INSTEAD-OF。
AFTER触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。DDL是SQL Server 2005的新触发器，允许响应数据库引擎中对象定义水平事件(例如:DROP TABLE语句)。INSTEAD-OF触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如:将INSTEAD-OF INSERT触发器附加到表，告诉数据库执行此触发器。
使用INSTEAD-OF触发器的理由
INSTEAD-OF触发器是SQL Sever功能强大的对象，允许开发人员转移数据库引擎完成不同的工作，以满足开发要求。其中的一个例子是在数据库的表中添加INSTEAD-OF触发器，当不需要修改表时，可以对表的内容进行回滚。使用此方法时，必须格外小心，因为任何指定的表修改之前，INSTEAD-OF触发器必须处于激活状态。
使用INSTEAD-OF触发器一个更充分理由是视图处理。在视图中添加INSTEAD-OF触发器后，则可创建更新的视图。可更新视图允许完整地提取数据库大纲，因此可以用此方法设计系统，而不需要担心OLTP数据库大纲的问题，并且取代数据修改一组标准视图集。
范例
Listing A
CREATE TABLE Products
(
ProductID SMALLINT IDENTITY(1,1) PRIMARY KEY,
Description VARCHAR(75),
Price MONEY NOT NULL
)
GO
CREATE TABLE Purchases
(
PurchaseID SMALLINT IDENTITY(1,1) PRIMARY KEY,
ProductID SMALLINT REFERENCES Products(ProductID),
PurchasePrice MONEY NOT NULL,
PurchaseDate SMALLDATETIME DEFAULT(GETDATE())
) |
为了更好地说明可更新视图概念，我们提供一个示例。在本例中，我们设计一个产品表(记录产品)，一个购买表(记录购买)。Listing A包含了创建表的脚本，运行此脚本后则得到示例中所要用到的表。运行Listing B脚本向表中添加数据。
Listing B
INSERT INTO Products(Description, Price) VALUES('Television',500)
INSERT INTO Products(Description, Price) VALUES('VCR',100)
INSERT INTO Products(Description, Price) VALUES('DVD_Player',125)
INSERT INTO Products(Description, Price) VALUES('Alarm_Clock',40)
INSERT INTO Products(Description, Price) VALUES('Camera',325)
INSERT INTO Products(Description, Price) VALUES('Projector',1500)
INSERT INTO Products(Description, Price) VALUES('XBox',400)
GO
INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 500)
INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(5, 325)
INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 525)
GO |
现在表中已经有数据了，我可以为这些表创建一些有意义的视图。请查看Listing C。
Listing C
CREATE VIEW vw_ProductPurchases
AS
SELECT
pr.ProductID,
pr.Description,
pr.Price AS ProductPrice,
pu.PurchasePrice,
pu.PurchaseDate
FROM
Products pr
INNER JOIN Purchases pu ON pr.ProductID = pu.ProductID
GO
|
这是个典型的产品水平的视图。它联合了数据库中的两个表，使得数据简单化了。但是，对于数据提取，使用视图则没有什么优势。在视图上附上INSTEAD-OF触发器后，则允许修改表，但是我不需要直接修改表中的数据。我使用Listing D中的代码在vw_ProductPurchases视图上创建一个INSTEAD-OF触发器。
|
Listing D
CREATE TRIGGER tr_vwProductPurchases ON vw_ProductPurchases
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS
(
SELECT TOP 1 *
FROM INSERTED
WHERE
ProductID IS NOT NULL AND
ISNULL(COALESCE(PurchasePrice, ProductPrice),0)>0
)
BEGIN
INSERT INTO Purchases
(
ProductID, PurchasePrice, PurchaseDate
)
SELECT
i.ProductID, COALESCE(PurchasePrice, ProductPrice), ISNULL(PurchaseDate, GETDATE())
FROM INSERTED i
END
ELSE
BEGIN
PRINT 'Adequate data not provided.'
END
END
|
请注意此INSTEAD OF触发器的声明。SQL Server创建的默认的触发器为AFTER触发器，因此，必须在触发器定义中指定INSTEAD OF子句。
触发器的第一条语句是“check”语句。本例中我使用此语句检测INSERTED表以确保显示ProductID字段，并且保证提供显示其他PurchasePrice 或 ProductPrice字段。
如果必要的数据通过INSERT语句都插入到视图中，则触发器将插入指定的值到数据表。下面即是视图的INSERT语句。
| INSERT INTO vw_ProductPurchases(ProductID, PurchasePrice) VALUES(1, 700) |
INSERT语句提供了有效的ProductID和PurchasePrice，这意味着新记录插入到Purchases表。
结论
INSTEAD-OF触发器具有强大的功能和灵活性。如果系统不大，使用视图系统提取数据大纲能够极大保护数据库程序。
上面的例子很简单，系统中所用到的复杂的触发器需要考虑安全性问题、时间开销和其他限制。
Tim Chapman是一个 SQL Server数据库管理员，他在Louisville， KY的一家银行工作，有7年的IT工作经验，通过了微软SQL Server 2000 和SQL Server 2005认证。