inserted テーブルと deleted テーブルの使用Use the inserted and deleted Tables

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

DML トリガー ステートメントでは、deleted テーブルおよび inserted テーブルという 2 つの特殊なテーブルが使用されます。DML trigger statements use two special tables: the deleted table and the inserted tables. SQL ServerSQL Server では、これらのテーブルを自動的に作成および管理します。automatically creates and manages these tables. これらの一時的なメモリ常駐型のテーブルを使用して、特定のデータ変更の影響をテストしたり、DML トリガー操作に条件を設定したりできます。You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. これらのテーブル内のデータを直接変更したり、これらのテーブルに対して CREATE INDEX などのデータ定義言語 (DDL) 操作を実行することはできません。You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

DML トリガーでは、inserted テーブルと deleted テーブルは主に次のことを実行するために使用されます。In DML triggers, the inserted and deleted tables are primarily used to perform the following:

  • テーブル間の参照整合性の拡張。Extend referential integrity between tables.

  • ビューを構成するベース テーブルでのデータの挿入または更新。Insert or update data in base tables underlying a view.

  • エラーのテストとエラー内容に基づく動作の実行。Test for errors and take action based on the error.

  • データ変更前と変更後のテーブルの状態の違いを検出し、その違いに基づいた動作の実行。Find the difference between the state of a table before and after a data modification and take actions based on that difference.

deleted テーブルには、DELETE ステートメントと UPDATE ステートメントの実行で影響を受けた行のコピーが格納されます。The deleted table stores copies of the affected rows during DELETE and UPDATE statements. DELETE ステートメントまたは UPDATE ステートメントの実行中、行はトリガー テーブルから削除され、deleted テーブルに転送されます。During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. 通常、deleted テーブルとトリガー テーブルには共通する行はありません。The deleted table and the trigger table ordinarily have no rows in common.

inserted テーブルには、INSERT ステートメントおよび UPDATE ステートメントの実行で影響を受けた行のコピーが格納されます。The inserted table stores copies of the affected rows during INSERT and UPDATE statements. 挿入トランザクションまたは更新トランザクションの実行時には、新しい行が inserted テーブルとトリガー テーブルの両方に追加されます。During an insert or update transaction, new rows are added to both the inserted table and the trigger table. inserted テーブルの行はトリガー テーブルの新しい行のコピーです。The rows in the inserted table are copies of the new rows in the trigger table.

更新トランザクションは、削除処理とそれに続く挿入処理の組み合わせと考えることができます。まず、deleted テーブルに古い行がコピーされ、その後、新しい行がトリガー テーブルと inserted テーブルにコピーされます。An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

トリガーの条件を設定するときには、トリガーを起動した操作に合わせて inserted テーブルと deleted テーブルを使用します。When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. INSERT ステートメントをテストするときに deleted テーブルを参照したり、DELETE ステートメントをテストするときに inserted テーブルを参照してもエラーは発生しませんが、このような場合は、これらのトリガー テスト用テーブルには行が含まれていません。Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

注意

トリガーの動作が、データ変更の影響のある行の数に依存する場合、複数行データ変更 (SELECT ステートメントに基づく INSERT、DELETE、または UPDATE) に @@ROWCOUNT の検査などのテストを使用し、適切な動作を実行する必要があります。If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL ServerSQL Server では、AFTER トリガー用の inserted テーブルおよび deleted テーブル内で text列、 ntext列、または image 列を参照することを禁止しています。does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. これらのデータ型は旧バージョンとの互換性のためだけに用意されているものです。However, these data types are included for backward compatibility purposes only. 大きなデータを格納するには、 varchar(max)nvarchar(max) 、および varbinary(max) データ型を使用することをお勧めします。The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. AFTER トリガーと INSTEAD OF トリガーでは両方とも、inserted テーブルおよび deleted テーブルで varchar(max)nvarchar(max) 、および varbinary(max) 型のデータがサポートされます。Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. 詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。For more information, see CREATE TRIGGER (Transact-SQL).

トリガーで inserted テーブルを使用してビジネス ルールを適用する例An Example of Using the inserted Table in a Trigger to Enforce Business Rules

CHECK 制約で参照できるのは、列レベルまたはテーブル レベルの制約が定義されている列のみであるため、テーブル間にまたがる制約 (ここでは、ビジネス ルール) はすべてトリガーとして定義する必要があります。Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

次の例では、DML トリガーを作成します。The following example creates a DML trigger. このトリガーでは、 PurchaseOrderHeader テーブルに新しい発注を挿入しようとしたときに、ベンダーの信用格付けが良好であるかどうかがチェックされます。This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. 挿入した発注に対応するベンダーの信用格付けを取得するには、 Vendor テーブルを参照し、inserted テーブルと結合する必要があります。To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. 信用格付けが低い場合は、メッセージが表示され、挿入は実行されません。If the credit rating is too low, a message is displayed and the insertion does not execute. この例では、複数行のデータの変更を許可していません。Note that this example does not allow for multirow data modifications. 詳細については、「 複数行のデータを処理するための DML トリガーの作成」を参照してください。For more information, see Create DML Triggers to Handle Multiple Rows of Data.

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 テーブルの使用Using the inserted and deleted Tables in INSTEAD OF Triggers

テーブルに定義された INSTEAD OF トリガーへ渡される inserted テーブルおよび deleted テーブルは、AFTER トリガーに渡される inserted テーブルおよび deleted テーブルと同じルールに従います。The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. inserted テーブルと deleted テーブルのフォーマットは、INSTEAD OF トリガーを定義したテーブルのフォーマットと同じです。The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. inserted テーブルおよび deleted テーブルの各列は、ベース テーブルの列に直接マップされます。Each column in the inserted and deleted tables maps directly to a column in the base table.

INSTEAD OF トリガーを含むテーブルを参照する INSERT ステートメントまたは UPDATE ステートメントで、列に値を指定するときに適用されるルールは、次に示すように、INSTEAD OF トリガーのないテーブルに適用されるルールと同じです。The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

  • 計算列または timestamp データ型の列の場合、値は指定できません。Values cannot be specified for computed columns or columns with a timestamp data type.

  • IDENTITY プロパティを持つ列の場合、そのテーブルに対して IDENTITY_INSERT が ON になっていない限り、値は指定できません。Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that table. IDENTITY_INSERT が ON になっている場合は、INSERT ステートメントで値を指定する必要があります。When IDENTITY_INSERT is ON, INSERT statements must supply a value.

  • INSERT ステートメントでは、DEFAULT 制約のないすべての NOT NULL 列に対して値を指定する必要があります。INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.

  • 計算列、ID 列、または timestamp 型の列以外で、NULL 値を許容する列、または DEFAULT 定義を持つ NOT NULL 列については、値の指定を省略できます。For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

INSERT、UPDATE、または DELETE の各ステートメントが INSTEAD OF トリガーを含むビューを参照する場合、 データベース エンジンDatabase Engine は、どのテーブルに対しても直接に動作を実行するのではなく、そのトリガーを呼び出します。When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the データベース エンジンDatabase Engine calls the trigger instead of taking any direct action against any table. 呼び出されたトリガーは、inserted テーブルおよび deleted テーブルの情報を使用して、ベース テーブルで要求された動作を実装するために必要なステートメントを作成する必要があります。その際、ビューに作成された inserted テーブルおよび deleted テーブルの情報の形式がベース テーブルのデータ形式と異なっていてもかまいません。The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built for the view is different from the format of the data in the base tables.

ビューに定義された INSTEAD OF トリガーに渡される inserted テーブルおよび deleted テーブルの形式は、ビューに定義された SELECT ステートメントの選択リストに一致します。The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. 例:For example:

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 列です。The result set for this view has three columns: an int column and two nvarchar columns. ビューで定義された INSTEAD OF トリガーに渡される inserted テーブルと deleted テーブルには、 という名前の int BusinessEntityID列、 という名前の nvarchar LName列、および という名前の nvarchar FName列があります。The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named BusinessEntityID, an nvarchar column named LName, and an nvarchar column named FName.

ビューの選択リストには、単一のベース テーブルの列に直接マップされない式を含めることができます。The select list of a view can also contain expressions that do not directly map to a single base-table column. ビューの式の中には、定数や関数の呼び出しなど、列を参照しない可能性があり、無視できるものがあります。Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. 複合式を使用して複数の列を参照できます。ただし、inserted テーブルおよび deleted テーブルでは、挿入された行ごとに 1 つの値のみを持つことができます。Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. このことは、複合式を持つ計算列を参照するビューの単純式にも当てはまります。The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. このような種類の式は、ビューの INSTEAD OF トリガーが処理する必要があります。An INSTEAD OF trigger on the view must handle these types of expressions.