INSTEAD OF INSERT 觸發程序

您可以對檢視或資料表定義 INSTEAD OF INSERT 觸發程序，以取代 INSERT 陳述式的標準動作。通常是對檢視定義 INSTEAD OF INSERT 觸發程序，以便在一或多個基底資料表中插入資料。

檢視選取清單中的資料行，可以是可設為 Null 值也可以是不可設為 Null 值的資料行。如果檢視資料行不允許有 Null 值，則 INSERT 陳述式必須提供資料行的值。如果定義檢視資料行的運算式包含如下的項目，則檢視資料行允許設為 Null：

• 允許 Null 之任何基底資料表資料行的參考

• 算術運算子

• 函數參考

• 含有可為 Null 值之子運算式的 CASE 或 COALESCE

• NULLIF

您可以使用 COLUMNPROPERTY 函數報告的 AllowsNull 屬性，來決定檢視資料行是否允許設為 Null 值。sp_help 預存程序也會報告哪些檢視資料行允許設為 Null 值。

參考含 INSTEAD OF INSERT 觸發程序之檢視的 INSERT 陳述式，必須為不允許 Null 值的每個檢視資料行提供值。這包含參考基底資料表中資料行的檢視資料行 (該基底資料表的輸入值不能指定)，如：

• 基底資料表中的計算資料行。

• 基底資料表中的識別欄位，其 IDENTITY INSERT 為 OFF。

• 具有 timestamp 資料類型的基底資料表資料行。

如果 INSTEAD OF INSERT 檢視表觸發程序使用 inserted 資料表中的資料對基底資料表產生 INSERT，它必須忽略這類資料行的值，所以在 INSERT 陳述式的選取清單中不能包含資料行。INSERT 陳述式可以為這些類型的資料行產生空的值。

例如，雖然 INSERT 陳述式必須為對應到基底資料表的識別 (Identity) 或計算資料行的檢視資料行，指定一個值，但它也可以提供預留位置值。當 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'。

inserted 資料表中的計算、識別及 timestamp 資料行所包含的值，在針對資料表指定的 INSTEAD OF INSERT 觸發程序中，與針對檢視指定的 INSTEAD OF 觸發程序中有所不同。

直接參考基底資料表的 INSERT 陳述式，不必為同時具有 DEFAULT 的 NOT NULL 資料行提供值。如果 INSERT 陳述式未提供值，則使用預設值。不過如果檢視中的簡單運算式參考具有 DEFAULT 的 NOT NULL 資料行，而此檢視又有一個 INSTEAD OF INSERT 觸發程序，則參考檢視的所有 INSERT 陳述式都必須為資料行提供一個值。有了這個值，才能建立傳遞給觸發程序的 inserted 資料表。必須為值設定一個慣例，以告知觸發程序應使用預設值。最佳慣例就是由 INSERT 陳述式提供預設值。

INSTEAD OF INSERT 觸發程序中的 deleted 資料表永遠都是空的。