次の方法で共有

INSTEAD OF トリガのデザイン

  • [アーティクル]

INSTEAD OF トリガを使用する主な利点は、更新可能ではなかったビューを更新できるようになることです。複数のベース テーブルに基づいたビューでは、INSTEAD OF トリガを使用して、複数のテーブルのデータを参照する挿入、更新、および削除の各操作をサポートする必要があります。INSTEAD OF トリガのもう 1 つの利点は、バッチの一部を拒否しながらバッチのその他の部分を正常に実行できるロジックをコード化できることです。

INSTEAD OF トリガを使用すると、次のような操作を実行できます。

  • バッチの一部を無視する。
  • バッチの一部の処理および問題のある行のログ記録を実行しない。
  • エラー状態が検出された場合、代替操作を実行する。
    ms175521.note(ja-jp,SQL.90).gifメモ :
    DELETE 連鎖操作または UPDATE 連鎖操作を使用して定義された外部キーを持つテーブルに対しては、INSTEAD OF DELETE トリガおよび INSTEAD OF UPDATE トリガを定義できません。

このロジックを INSTEAD OF トリガの一部としてコード化すると、データにアクセスするすべてのアプリケーションでそのロジックを再実装する必要がなくなります。

例

次に示す一連の Transact-SQL ステートメントでは、INSTEAD OF トリガを使用して、ビューから 2 つのベース テーブルを更新します。さらに、エラーを処理するための方法も示します。

  • Person テーブルへの重複行の挿入は無視されます。挿入に関する情報は、PersonDuplicates テーブルに記録されます。
  • EmployeeTable に重複行を挿入すると、UPDATE ステートメントに変わり、重複キー違反を生成せずに現在の情報を EmployeeTable に取り込みます。

次の Transact-SQL ステートメントでは、2 つのベース テーブル、ビュー、エラーを記録するためのテーブル、およびビューの 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)
   )

次のビューでは、2 つのテーブルの個人に関するデータがすべて示されます。

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 トリガにより、1 つのビューから複数のベース テーブルに行が挿入されます。重複した社会保障番号を含む行の挿入が試行されると、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 の参考資料の入手