inserted と deleted テーブルの使用

  • [アーティクル]

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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

inserted と deleted テーブルについて

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

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

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

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

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

deleted テーブルには、DELETE または UPDATE ステートメントによって変更される前に、影響を受ける行のコピーがトリガー テーブルに格納されます (トリガー テーブルは DML トリガーが実行されるテーブルです)。 DELETE または UPDATE ステートメントの実行中、影響を受ける行は最初にトリガー テーブルからにコピーされ、deleted テーブルに転送されます。

inserted テーブルには、INSERT または UPDATE ステートメントの後に、新しいまたは変更された行のコピーが格納されます。 INSERT または UPDATE ステートメントの実行中に、トリガー テーブルの新しいまたは変更された行が、inserted テーブルにコピーされます。 inserted テーブルの行は、トリガー テーブルの新しいまたは更新された行のコピーです。

更新トランザクションは、削除操作の後に挿入される操作に似ています。 UPDATE ステートメントの実行中に、次の一連のイベントが発生します。

  1. 元の行は、トリガー テーブルから deleted テーブルにコピーされます。
  2. トリガー テーブルは、UPDATE ステートメントの新しい値で更新されます。
  3. トリガー テーブルの更新された行は、inserted テーブルにコピーされます。

これにより、(deleted テーブル内の) 更新前の行の内容と、(inserted テーブル内の) 更新後の新しい行の値を比較できます。

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

Note

トリガーの動作が、データ変更の影響のある行の数に依存する場合、複数行データ変更 (SELECT ステートメントに基づく INSERT、DELETE、または UPDATE) に @@ROWCOUNT の検査などのテストを使用し、適切な動作を実行する必要があります。 詳細については、「 複数行のデータを処理するための DML トリガーの作成」を参照してください。

SQL Server では、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 テーブルと結合する必要があります。 信用格付けが低い場合は、メッセージが表示され、挿入は実行されません。

USE AdventureWorks2022;
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 (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.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 AdventureWorks2022;  
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 テーブルには、 という名前の int BusinessEntityID列、 という名前の nvarchar LName列、および という名前の nvarchar FName列があります。

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

パフォーマンスに関する考慮事項

inserted テーブルと deleted テーブルはメモリ常駐の仮想テーブルであるため、統計やインデックスなどのプロパティは使用できません。 これらのテーブルからはいくつかのカーディナリティ情報が公開されますが、一時的に格納される行の数を検討する際には注意が必要です。 これらのテーブルに多数の行を挿入し、クエリを実行したり他のテーブルと結合したりすると、準最適なクエリ プランが作成され、クエリの実行速度が低下する可能性があります。 クエリ パフォーマンスのニーズを満たすように、慎重にアプリケーションを設計してテストしてください。

次のステップ

詳細については、「DML トリガー」の概要を参照してください。