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 テーブルを参照してもエラーは発生しませんが、このような場合は、これらのトリガ テスト用テーブルには行が含まれていません。
.gif "注意") 注意 |
|---|
トリガの動作が、データ変更の影響のある行の数に依存する場合、複数行データ変更 (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 トリガの複数行に関する注意点」を参照してください。 |
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.VendorID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
-- 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 ステートメントの選択リストに一致します。次に例を示します。
CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
このビューの結果セットには 3 列があり、1 つは int 列で、後の 2 つは nvarchar 列です。ビューで定義された INSTEAD OF トリガに渡される inserted テーブルと deleted テーブルには、EmployeeID という名前の int 列、LName という名前の nvarchar 列、および FName という名前の nvarchar 列があります。
ビューの選択リストには、単一のベース テーブルの列に直接マップされない式を含めることができます。ビューの式の中には、定数や関数の呼び出しなど、列を参照しない可能性があり、無視できるものがあります。複合式を使用して複数の列を参照できます。ただし、inserted テーブルおよび deleted テーブルでは、挿入された行ごとに 1 つの値のみを持つことができます。このことは、複合式を持つ計算列を参照するビューの単純式にも当てはまります。このような種類の式は、ビューの INSTEAD OF トリガが処理する必要があります。詳細については、「INSTEAD OF トリガの式と計算列」を参照してください。