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 觸發程序中 inserted 資料表的值 |
檢視的 INSTEAD OF INSERT 觸發程序中 inserted 資料表的值 |
---|---|---|
是一個計算資料行 |
計算運算式 |
使用者指定的值或 NULL |
擁有 IDENTITY 屬性。 |
如果 IDENTITY_INSERT 是 OFF,則為 0,如果 IDENTITY_INSERT 是 ON,則為指定的值 |
使用者指定的值或 NULL |
具有 timestamp 資料類型。 |
如果資料行不允許 Null 值則為二進位的 0,如果允許 Null 值則為 NULL |
使用者指定的值或 NULL |
直接參考基底資料表的 INSERT 陳述式,不必為同時具有 DEFAULT 的 NOT NULL 資料行提供值。如果 INSERT 陳述式未提供值,則使用預設值。不過如果檢視中的簡單運算式參考具有 DEFAULT 的 NOT NULL 資料行,而此檢視又有一個 INSTEAD OF INSERT 觸發程序,則參考檢視的所有 INSERT 陳述式都必須為資料行提供一個值。有了這個值,才能建立傳遞給觸發程序的 inserted 資料表。必須為值設定一個慣例,以告知觸發程序應使用預設值。最佳慣例就是由 INSERT 陳述式提供預設值。
INSTEAD OF INSERT 觸發程序中的 deleted 資料表永遠都是空的。