设计 INSTEAD OF 触发器

设计 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 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,Comment

      FROM inserted

ELSE

-- Log 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 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,Comment

      FROM inserted

ELSE

--If 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,

          Comment = I.Comment

   FROM EmployeeTable E, inserted I

   WHERE E.SSN = I.SSN

END