INSTEAD OF INSERT トリガー
INSTEAD OF INSERT トリガーはビューまたはテーブルに定義でき、INSERT ステートメントの標準動作を置き換える働きをします。一般的に INSTEAD OF INSERT トリガーは、1 つ以上のベース テーブルにデータを追加する目的で、ビューに定義します。
ビューの選択リスト内の列は、NULL 値を許容する場合も許容しない場合もあります。ビュー列が NULL 値を許容しない場合は、INSERT ステートメントを使用してその列に値を提供する必要があります (IDENTITY 列または計算列の場合は推定できます)。ビュー列を定義している式に次のような項目が含まれている場合、そのビュー列では NULL 値が許容されます。
ベース テーブル内にある NULL 値を許容する列への参照
算術演算子
関数への参照
NULL 値を許容するサブ式を含んだ CASE または COALESCE
NULLIF
COLUMNPROPERTY 関数が返す AllowsNull プロパティを使用すると、ビュー列が NULL 値を許容するかどうかを判断できます。sp_help ストアド プロシージャを使用して、どのビュー列で NULL 値が許容されるかを判断することもできます。
INSTEAD OF INSERT トリガーが設定されたビューを参照する INSERT ステートメントは、NULL 値を許容しないすべてのビュー列に値を提供する必要があります。このことは、次に示すような、入力値を指定できないベース テーブル列を参照するビュー列にも当てはまります。
ベース テーブルの計算列
ベース テーブル内の、IDENTITY INSERT が OFF に設定されている ID 列
timestamp データ型のベース テーブル列
INSTEAD OF INSERT ビュー トリガーは、inserted テーブル内のデータを使用してベース テーブルに対する INSERT を生成する場合、INSERT ステートメントの選択リストから除外することによって、上記のような列の値を無視する必要があります。このような列に対して INSERT ステートメントはダミーの値を生成できます。
たとえば、ベース テーブルの ID 列または計算列にマップするビュー列の値を INSERT ステートメントで指定する必要がある場合、プレースホルダー値を指定できます。INSTEAD OF トリガーは、ベース テーブルに値を挿入する INSERT ステートメントを生成するときに、提供された値を無視できます。
次のステートメントでは、この処理を示すテーブル、ビュー、およびトリガーを作成しています。
CREATE TABLE BaseTable
(ID int PRIMARY KEY IDENTITY(1,1),
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
);
GO
--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT ID, Color, Material, ComputedCol
FROM BaseTable;
GO
--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.ID and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END;
GO
BaseTable を直接参照する INSERT ステートメントは、ID 列と ComputedCol 列に値を指定することはできません。次に例を示します。
--A correct INSERT statement that skips the ID and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
VALUES (N'Red', N'Cloth');
--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM BaseTable;
--An incorrect statement that tries to supply a value for the
--ID and ComputedCol columns.
INSERT INTO BaseTable
VALUES (2, N'Green', N'Wood', N'GreenWood');
ただし、InsteadView を参照する INSERT ステートメントは、次のように ID と ComputedCol に値を指定する必要があります。
--A correct INSERT statement supplying dummy values for the
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (ID, Color, Material, ComputedCol)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM InsteadView;
InsteadTrigger に渡される inserted テーブルは、NULL 値を許容しない ID 列と ComputedCol 列を組み込んで構築されています。このため、ビューを参照する INSERT ステートメントでは、これらの列に値を指定する必要があります。値 999 と N'XXXXXX' が InsteadTrigger に渡されていますが、トリガー内の INSERT ステートメントは inserted.ID も inserted.ComputedCol も選択していません。したがってこれらの値は無視されます。実際に BaseTable に挿入される行の ID には 2 が、ComputedCol には N'BluePlastic' が格納されています。
INSTEAD OF INSERT トリガーをテーブルに指定した場合と INSTEAD OF トリガーをビューに指定した場合では、inserted テーブルに格納される計算列、ID 列、および timestamp 列の値が異なります。
ベース テーブル列 |
テーブルに INSERT トリガーを指定した場合に inserted テーブルに格納される値 |
ビューに INSERT トリガーを指定した場合に inserted テーブルに格納される値 |
|---|---|---|
計算列である場合 |
計算式 |
ユーザー指定の値または NULL |
IDENTITY プロパティがある場合 |
IDENTITY_INSERT が OFF の場合は 0、ON の場合は指定された値 |
ユーザー指定の値または NULL |
timestamp 型の場合 |
列で NULL 値を許容しない場合はバイナリ型のゼロ、NULL 値を許容する場合は NULL |
ユーザー指定の値または NULL |
DEFAULT 定義が設定された NOT NULL 列には、ベース テーブルを直接参照する INSERT ステートメントが値を指定する必要はありません。INSERT ステートメントで値を指定しない場合は、既定値が使用されます。ただし、DEFAULT 定義の設定されている NOT NULL 列が、INSTEAD OF INSERT トリガーを設定したビューで単純式によって参照される場合、そのビューを参照する INSERT ステートメントはすべて、その列に値を提供する必要があります。この値は、トリガーに渡す inserted テーブルを作成するために必要です。既定値を使用する必要があることをトリガーに知らせる値には、規則の適用が必要です。最も効果的な規則は、INSERT ステートメントによって既定値を指定する方法です。
INSTEAD OF INSERT トリガー内の deleted テーブルは常に空です。