設計 INSTEAD OF 觸發程序

INSTEAD OF 觸發程序最主要的優點，就是讓無法更新的檢視支援更新。以多個基底資料表為基礎的檢視必須使用 INSTEAD OF 觸發程序來支援在多個資料表中參考資料的插入、更新與刪除動作。INSTEAD OF 觸發程序的另一項優點在於讓您編寫邏輯時可拒絕批次的某些部份，讓批次的其他部份成功執行。

INSTEAD OF 觸發程序可執行的動作如下：

• 忽略批次的某些部份。
• 不處理批次的某個部份與記錄問題資料列。
• 發生錯誤狀況時採取替代動作。

  附註：
  INSTEAD OF DELETE 與 INSTEAD OF UPDATE 觸發程序不能定義於以 DELETE 或 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 中的重複資料列會變更為 UPDATE 陳述式。

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 觸發程序

說明及資訊

取得 SQL Server 2005 協助