INSTEAD OF INSERT トリガ
INSTEAD OF INSERT トリガはビューまたはテーブルに定義でき、INSERT ステートメントの標準動作を置き換える働きをします。一般的に INSTEAD OF INSERT トリガは、1 つ以上のベース テーブルにデータを追加する目的で、ビューに定義します。
ビューの選択リスト内の列は、NULL 値を許容する場合も許容しない場合もあります。ビュー列が NULL 値を許容しない場合は、INSERT ステートメントを使用してその列に値を提供する必要があります。ビュー列を定義している式に次のような項目が含まれている場合、そのビュー列では 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
(PrimaryKey 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 PrimaryKey, 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.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END
GO
BaseTable を直接参照する INSERT ステートメントは、PrimaryKey 列と ComputedCol 列に値を指定することはできません。次に例を示します。
--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
VALUES (N'Red', N'Cloth')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
--An incorrect statement that tries to supply a value for the
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable
VALUES (2, N'Green', N'Wood', N'GreenWood')
ただし、InsteadView を参照する INSERT ステートメントは、次のように PrimaryKey と ComputedCol に値を指定する必要があります。
--A correct INSERT statement supplying dummy values for the
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
InsteadTrigger に渡される inserted テーブルは、NULL 値を許容しない PrimaryKey 列と ComputedCol 列を組み込んで構築されています。このため、ビューを参照する INSERT ステートメントでは、これらの列に値を指定する必要があります。値 999 と N'XXXXXX' が InsteadTrigger に渡されていますが、トリガ内の INSERT ステートメントは inserted.PrimaryKey も inserted.ComputedCol も選択していません。したがってこれらの値は無視されます。実際に BaseTable に挿入される行の PrimaryKey には 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 テーブルは常に空です。
参照
その他の技術情報
COLUMNPROPERTY (Transact-SQL)
sp_help (Transact-SQL)