• 项目

设计 INSTEAD OF 触发器

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

INSTEAD OF 触发器可以进行以下操作：

  • 忽略批处理中的某些部分。

  • 不处理批处理中的某些部分并记录有问题的行。

  • 如果遇到错误情况则采取备用操作。

    注意注意

    对于含有使用 DELETE 或 UPDATE 级联操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。

将此逻辑作为 INSTEAD OF 触发器的一部分进行编码，可避免所有访问数据的应用程序必须重新实现该逻辑。

示例

在下列 Transact-SQL 语句序列中，INSTEAD OF 触发器更新视图中的两个基表。另外，显示以下处理错误的方法：

  • 忽略对 Person 表的重复插入，并且插入的信息将记录在 PersonDuplicates 表中。

  • 将对 EmployeeTable 的重复插入转变为 UPDATE 语句，该语句将当前信息检索至 EmployeeTable，而不会产生重复键冲突。

Transact-SQL 语句创建两个基表、一个视图、一个记录错误表和视图上的 INSTEAD OF 触发器。以下表将个人数据和业务数据分开并且是视图的基表。

CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )

下面的视图使用某个人的两个表中的所有相关数据建立报表。

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

可记录对插入具有重复的社会保障号的行的尝试。PersonDuplicates 表记录插入的值、尝试插入的用户的姓名和插入的时间。

CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

INSTEAD OF 触发器将行插入到单个视图的多个基表中。在 PersonDuplicates 表中记录了插入具有重复社会保障号的行的尝试。EmployeeTable 中的重复行将更改为更新语句。

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

请参阅

概念

设计 DML 触发器