挿入されたテーブルと削除されたテーブルを使用する

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

DML トリガーでは、挿入されたテーブルと削除されたテーブルは、主に次の処理を実行するために使用されます。

テーブル間の参照整合性を拡張します。
ビューの基になるベーステーブルにデータを挿入または更新します。
エラーをテストし、エラーに基づいてアクションを実行します。
データ変更の前後のテーブルの状態の違いを見つけ、その違いに基づいてアクションを実行します。

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

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

更新トランザクションは、削除操作とそれに続く挿入操作に似ています。古い行が最初に削除されたテーブルにコピーされ、次に新しい行がトリガーテーブルと挿入されたテーブルにコピーされます。

トリガー条件を設定する場合は、トリガーを発生したアクションに対して、挿入されたテーブルと削除されたテーブルを適切に使用します。 DELETE のテスト時に INSERT または挿入されたテーブルをテストするときに削除されたテーブルを参照してもエラーは発生しませんが、このような場合、これらのトリガーテストテーブルには行は含まれません。

注

トリガーアクションがデータ変更効果の行数に依存する場合は、複数行のデータ変更 (SELECT ステートメントに基づく INSERT、DELETE、または UPDATE) のテスト (@@ROWCOUNTの検査など) を使用し、適切なアクションを実行します。

SQL Server 2014 では、AFTER トリガーの挿入および削除されたテーブルの text、 ntext、または image 列参照を使用できません。ただし、これらのデータ型は下位互換性のみを目的として含まれています。大きなデータに適したストレージは、 varchar(max)、 nvarchar(max)、および varbinary(max) のデータ型を使用することです。 AFTER トリガーと INSTEAD OF トリガーの両方で、挿入されたテーブルと削除されたテーブルのデータの varchar(max)、 nvarchar(max)、および varbinary(max) がサポートされます。詳細については、「 CREATE TRIGGER (Transact-SQL)」をご覧ください。

トリガーに挿入されたテーブルを使用してビジネスルールを適用する例

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

次の例では、DML トリガーを作成します。このトリガーは、新しい発注書を PurchaseOrderHeader テーブルに挿入しようとしたときに、仕入先の信用格付けが適切であることを確認します。挿入した発注書に対応する仕入先の信用格付けを取得するには、 Vendor テーブルを参照し、挿入されたテーブルと結合する必要があります。信用格付が低すぎると、メッセージが表示され、挿入は実行されません。この例では、複数行データの変更は許可されないことに注意してください。詳細については、「複数行のデータを処理する 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 トリガーでの挿入および削除されたテーブルの使用

テーブルで定義されている INSTEAD OF トリガーに渡される挿入テーブルと削除テーブルは、AFTER トリガーに渡される挿入テーブルと削除テーブルと同じ規則に従います。挿入されたテーブルと削除されたテーブルの形式は、INSTEAD OF トリガーが定義されているテーブルの形式と同じです。挿入されたテーブルと削除されたテーブルの各列は、ベーステーブルの列に直接マップされます。

INSTEAD OF トリガーを使用してテーブルを参照する INSERT ステートメントまたは UPDATE ステートメントで列の値を指定する必要がある場合に関する次の規則は、テーブルに INSTEAD OF トリガーがない場合と同じです。

timestampデータ型の計算列または列には値を指定できません。
そのテーブルに対して IDENTITY_INSERT が ON でない限り、IDENTITY プロパティを持つ列には値を指定できません。 IDENTITY_INSERTが ON の場合、INSERT ステートメントは値を指定する必要があります。
INSERT ステートメントは、DEFAULT 制約を持たないすべての NOT NULL 列に値を指定する必要があります。
計算列、ID 列、または timestamp 列を除くすべての列の場合、NULL を許容する列、または DEFAULT 定義を持つ NOT NULL 列の値は省略可能です。

INSERT、UPDATE、または DELETE ステートメントが INSTEAD OF トリガーを持つビューを参照すると、データベースエンジンはテーブルに対して直接アクションを実行する代わりにトリガーを呼び出します。トリガーは、挿入および削除されたテーブルに表示される情報を使用して、ビュー用に作成された挿入および削除されたテーブルの情報の形式がベーステーブルのデータの形式と異なる場合でも、要求されたアクションをベーステーブルに実装するために必要なステートメントを作成する必要があります。

ビューで定義されている INSTEAD OF トリガーに渡される挿入テーブルと削除されたテーブルの形式は、ビューに対して定義されている 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;

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

ビューの選択リストには、1 つのベーステーブル列に直接マップされない式を含めることもできます。定数や関数の呼び出しなど、一部のビュー式は列を参照せず、無視できます。複合式は複数の列を参照できますが、挿入されたテーブルと削除されたテーブルの値は、挿入された行ごとに 1 つだけです。複雑な式を持つ計算列を参照する場合、ビュー内の単純な式にも同じ問題が適用されます。ビューの INSTEAD OF トリガーは、これらの種類の式を処理する必要があります。

Last updated on 2017-06-13

次の方法で共有

挿入されたテーブルと削除されたテーブルを使用する

INSTEAD OF トリガーでの挿入および削除されたテーブルの使用

その他のリソース