INSTEAD OF INSERT 触发器
可以为视图或表定义 INSTEAD OF INSERT 触发器来替换 INSERT 语句的标准操作。通常会为视图定义 INSTEAD OF INSERT 触发器以在一个或多个基表中插入数据。
视图选择列表中的列可以为空,也可以不为空。如果视图列不允许使用空值,则 INSERT 语句必须为该列提供值。如果定义视图列的表达式包括下列项,则视图列允许使用空值:
- 对任何允许使用空值的基表列的引用
- 算术运算符
- 对函数的引用
- 具有可为空值的子表达式的 CASE 或 COALESCE
- NULLIF
可以使用 COLUMNPROPERTY 函数报告的 AllowsNull 属性来确定视图列是否允许使用空值。sp_help 存储过程还报告哪些视图列允许使用空值。
引用具有 INSTEAD OF INSERT 触发器的视图的 INSERT 语句必须为每个不允许使用空值的视图列提供值。此操作包括引用基表列的视图列(该基表不能指定输入值),如:
- 基表中的计算列。
- 基表中 IDENTITY INSERT 为 OFF 的标识列。
- 具有 timestamp 数据类型的基表列。
如果 INSTEAD OF INSERT 视图触发器使用所插入表中的数据对基表生成 INSERT,则它必须通过排除 INSERT 语句选择列表中的列来忽略上述列的值。INSERT 语句可以为这些类型的列生成虚值。
例如,虽然 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
的所插入表是由不可为空值的 PrimaryKey
列和 ComputedCol
列生成的,因此引用该视图的 INSERT
语句必须为那些列提供值。值 999
和 N'XXXXXX'
将传递到 InsteadTrigger
,但是触发器中的 INSERT
语句未选择 inserted.PrimaryKey
或 inserted.ComputedCol
;因此,将忽略这两个值。实际插入到 BaseTable
的行在 PrimaryKey
中的值为 2
,在 ComputedCol
中的值为 N'BluePlastic'
。
表的指定 INSTEAD OF INSERT 触发器与视图的指定 INSTEAD OF 触发器相比,所插入表中包含的计算列、标识列和 timestamp 列的值不同。
基表列 | 表的任何 INSERT 触发器的所插入表中的值 | 视图的 INSTEAD OF INSERT 触发器的所插入表中的值 |
---|---|---|
是计算列。 |
计算表达式 |
用户指定值或 NULL |
具有 IDENTITY 属性。 |
如果 IDENTITY_INSERT 为 OFF,则为 0;如果 IDENTITY_INSERT 为 ON,则为指定值 |
用户指定值或 NULL |
具有 timestamp 数据类型。 |
如果该列不允许使用空值,则为二进制零;如果该列允许使用空值,则为 NULL |
用户指定值或 NULL |
直接引用基表的 INSERT 语句不必为同样具有 DEFAULT 定义的 NOT NULL 列提供值。如果 INSERT 语句不提供值,则使用默认值。但是,如果具有 DEFAULT 定义的 NOT NULL 列被具有 INSTEAD OF INSERT 触发器的视图中的简单表达式引用,则引用该视图的任何 INSERT 语句必须为该列提供值。此值对于生成传递到触发器的所插入表是必需的。对发信号通知触发器应使用默认值的值必须具有相应的约定。最佳约定是让 INSERT 语句提供默认值。
INSTEAD OF INSERT 触发器中删除的表始终是空的。