• [アーティクル]

inserted テーブルと deleted テーブルの使用

DML トリガー ステートメントでは、deleted と inserted という 2 つの特殊なテーブルを使用します。これらのテーブルは SQL Server により自動的に作成され管理されます。これらの一時的なメモリ常駐型のテーブルを使用して、特定のデータ変更の影響をテストしたり、DML トリガー操作に条件を設定したりできます。これらのテーブル内のデータを直接変更したり、これらのテーブルに対して CREATE INDEX などのデータ定義言語 (DDL) 操作を実行することはできません。

DML トリガーでは、inserted テーブルと deleted テーブルは主に次のことを実行するために使用されます。

  • テーブル間の参照整合性の拡張。

  • ビューを構成するベース テーブルでのデータの挿入または更新。

  • エラーのテストとエラー内容に基づく動作の実行。

  • データ変更前と変更後のテーブルの状態の違いを検出し、その違いに基づいた動作の実行。

deleted テーブルには、DELETE ステートメントと UPDATE ステートメントの実行で影響を受けた行のコピーが格納されます。DELETE ステートメントまたは UPDATE ステートメントの実行中、行はトリガー テーブルから削除され、deleted テーブルに転送されます。通常、deleted テーブルとトリガー テーブルには共通する行はありません。

inserted テーブルには、INSERT ステートメントおよび UPDATE ステートメントの実行で影響を受けた行のコピーが格納されます。挿入トランザクションまたは更新トランザクションの実行時には、新しい行が inserted テーブルとトリガー テーブルの両方に追加されます。inserted テーブルの行はトリガー テーブルの新しい行のコピーです。

更新トランザクションは、削除処理とそれに続く挿入処理の組み合わせと考えることができます。まず、deleted テーブルに古い行がコピーされ、その後、新しい行がトリガー テーブルと inserted テーブルにコピーされます。

トリガーの条件を設定するときには、トリガーを起動した操作に合わせて inserted テーブルと deleted テーブルを使用します。INSERT ステートメントをテストするときに deleted テーブルを参照したり、DELETE ステートメントをテストするときに inserted テーブルを参照してもエラーは発生しませんが、このような場合は、これらのトリガー テスト用テーブルには行が含まれていません。

注意注

トリガーの動作が、データ変更の影響のある行の数に依存する場合、複数行データ変更 (SELECT ステートメントに基づく INSERT、DELETE、または UPDATE) に @@ROWCOUNT の検査などのテストを使用し、適切な動作を実行する必要があります。

SQL Server 2008 では、AFTER トリガー用の inserted テーブルおよび deleted テーブル内で text 列、ntext 列、または image 列を参照することを禁止しています。これらのデータ型は旧バージョンとの互換性のためだけに用意されているものです。大量のデータのストレージには、varchar(max) データ型、nvarchar(max) データ型、および varbinary(max) データ型を使用することをお勧めします。AFTER トリガーと INSTEAD OF トリガーでは両方とも、inserted および deleted テーブルの varchar(max)、nvarchar(max)、および varbinary(max) 型のデータがサポートされます。詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。

トリガーで inserted テーブルを使用してビジネス ルールを適用する例

CHECK 制約で参照できるのは、列レベルまたはテーブル レベルの制約が定義されている列のみであるため、テーブル間にまたがる制約 (ここでは、ビジネス ルール) はすべてトリガーとして定義する必要があります。

次の例では、DML トリガーを作成します。このトリガーでは、PurchaseOrderHeader テーブルに新しい発注を挿入しようとしたときに、ベンダーの信用格付けが良好であるかどうかがチェックされます。挿入した発注に対応するベンダーの信用格付けを取得するには、Vendor テーブルを参照し、inserted テーブルと結合する必要があります。信用格付けが低い場合は、メッセージが表示され、挿入は実行されません。

注意注

複数の行を更新する DML AFTER トリガーの例については、「DML トリガーの複数行に関する注意点」を参照してください。

 
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

GO
-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261    
,1652   
,4  
,GETDATE()
,GETDATE()
,44594.55   
,3567.564   
,1114.8638);
GO

INSTEAD OF トリガー内での inserted テーブルと deleted テーブルの使用

テーブルに定義された INSTEAD OF トリガーへ渡される inserted テーブルおよび deleted テーブルは、AFTER トリガーに渡される inserted テーブルおよび deleted テーブルと同じルールに従います。inserted テーブルと deleted テーブルのフォーマットは、INSTEAD OF トリガーを定義したテーブルのフォーマットと同じです。inserted テーブルおよび deleted テーブルの各列は、ベース テーブルの列に直接マップされます。

INSTEAD OF トリガーを含むテーブルを参照する INSERT ステートメントまたは UPDATE ステートメントで列に値を指定するときに適用されるルールは、次に示すように、INSTEAD OF トリガーのないテーブルに適用されるルールと同じです。

  • 計算列または timestamp データ型の列の場合、値は指定できません。

  • IDENTITY プロパティを持つ列の場合、そのテーブルに対して IDENTITY_INSERT が ON になっていない限り、値は指定できません。IDENTITY_INSERT が ON になっている場合は、INSERT ステートメントで値を指定する必要があります。

  • INSERT ステートメントでは、DEFAULT 制約のないすべての NOT NULL 列に対して値を指定する必要があります。

  • 計算列、ID 列、または timestamp 型の列以外で、NULL 値を許容する列、または DEFAULT 定義を持つ NOT NULL 列については、値の指定を省略できます。

INSERT、UPDATE、または DELETE の各ステートメントが INSTEAD OF トリガーを含むビューを参照する場合、データベース エンジンは、どのテーブルに対しても直接に動作を実行するのではなく、そのトリガーを呼び出します。呼び出されたトリガーは、inserted テーブルおよび deleted テーブルの情報を使用して、ベース テーブルで要求された動作を実装するために必要なステートメントを作成する必要があります。その際、ビューに作成された inserted テーブルおよび deleted テーブルの情報の形式がベース テーブルのデータ形式と異なっていてもかまいません。

ビューに定義された INSTEAD OF トリガーに渡される inserted テーブルおよび deleted テーブルの形式は、ビューに定義された SELECT ステートメントの選択リストに一致します。次に例を示します。

USE AdventureWorks2008R2;
GO
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)
AS
SELECT e.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS e 
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;

このビューの結果セットには 3 列があり、1 つは int 列で、後の 2 つは nvarchar 列です。ビューで定義された INSTEAD OF トリガーに渡される inserted テーブルと deleted テーブルには、BusinessEntityID という名前の int 列、LName という名前の nvarchar 列、および FName という名前の nvarchar 列があります。

ビューの選択リストには、単一のベース テーブルの列に直接マップされない式を含めることができます。ビューの式の中には、定数や関数の呼び出しなど、列を参照しない可能性があり、無視できるものがあります。複合式を使用して複数の列を参照できます。ただし、inserted テーブルおよび deleted テーブルでは、挿入された行ごとに 1 つの値のみを持つことができます。このことは、複合式を持つ計算列を参照するビューの単純式にも当てはまります。このような種類の式は、ビューの INSTEAD OF トリガーが処理する必要があります。詳細については、「INSTEAD OF トリガーの式と計算列」を参照してください。

関連項目

概念

DML トリガの実装