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

[アーティクル]
06/13/2017

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 テーブルを参照してもエラーは発生しませんが、このような場合は、これらのトリガーテスト用テーブルには行が含まれていません。

Note

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

SQL Server 2014 では、AFTER トリガーの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 トリガーの作成」を参照してください。

USE AdventureWorks2012;
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 AdventureWorks2012;  
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 トリガーが処理する必要があります。

次の方法で共有

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

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

その他のリソース