34,838
社区成员




--测试表建立
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
PrimaryKey Color Material ComputedCol
1 Red Cloth RedCloth
--下面语句返回错误,无法为计算列提供值,报错
--An incorrect statement that tries to supply a value for the
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable(Color, Material,ComputedCol)
VALUES ( N'Green', N'Wood', N'GreenWood')
>[Error] Script lines: 40-42 ------------------------
不能修改列 "ComputedCol",因为它是计算列,或者是 UNION 运算符的结果。
Msg: 271, Level: 16, State: 1, Procedure: , Line: 1
--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
--下面没有提供值,报错
INSERT INTO InsteadView (Color, Material)
VALUES (N'Yellow', N'Video')
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
INSERT INTO InsteadView (PRIMARYKEY,Color, Material,COMPUTEDCOL)
VALUES (null,N'Yellow', N'Card',null)
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
PrimaryKey Color Material ComputedCol
1 Red Cloth RedCloth
2 Blue Plastic BluePlastic
3 Yellow Video YellowVideo
4 Yellow Card YellowCard
看来不是这个意思