SqlServer INSTEAD OF UPDATE 视图触发器问题

源于系统中的 INSTEAD OF UPDATE 视图触发器关联表更新时，发生了错误如下：

消息 414，级别 16，状态 1，第 1 行
不允许使用 UPDATE，因为该语句会更新视图 "VTestTab"，而该视图参与联接并且有一个 INSTEAD OF UPDATE 触发器。

于是测试该触发器的执行原理是什么！~

说明：

视图只能被 INSTEAD OF 触发器引用，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器
INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图，否则 SQL Server 将引发错误

创建测试环境：

--DROP TABLE [TestTab]--TRUNCATE TABLE [TestTab]--创建表CREATE TABLE [dbo].[TestTab]([id] [int] NOT NULL,[name] [varchar](50) NOT NULL,[insertDate] [datetime] NOT NULL,[value] [numeric](14, 4) NULL,[info] [varchar](20) NULL,CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED ([id] ASC)) ON [PRIMARY]GO--创建视图CREATE VIEW [dbo].[VTestTab]ASSELECT [id] ,[name],[insertDate],[value],[info]FROM [dbo].[TestTab]GO--创建视图更新触发器(主要为这个引发的问题!)CREATE TRIGGER [dbo].[tgr_VTestTab_update]        ON [dbo].[VTestTab] INSTEAD OF UPDATE        AS  UPDATE [VTestTab] SET [name] = T2.[name],[insertDate] = T2.[insertDate],[value] = T2.[value],[info] = T2.[info]FROM [VTestTab] AS t1, inserted AS t2 WHERE t1.id = t2.idGO--插入数据到视图INSERT INTO [VTestTab]SELECT 1,'kk',GETDATE(),0,''UNION ALLSELECT 2,'JJ',GETDATE(),5,'HH'UNION ALLSELECT 3,'SS',GETDATE(),10,''UNION ALLSELECT 4,'MM',GETDATE(),0,NULLUNION ALLSELECT 5,'YY',GETDATE(),11,''GO--创建另一个表(或 实体表),稍后用于关联更新SELECT * INTO #TestTab FROM [VTestTab] --当前表、视图、临时表SELECT [id],[name],[insertDate],[value],[info] FROM [dbo].[TestTab]SELECT * FROM [dbo].[VTestTab]SELECT * FROM [dbo].#TestTab

现在对单个视图更新：

--对视图更新UPDATE [VTestTab] SET [value]=100 WHERE id = 1

对单个视图更新,结果正常,但是执行了2次!~执行计划可以看到！~

是不是真的执行了两次？！

官方说明为：
如果为视图定义的 INSTEAD OF 触发器对视图执行了一条通常会再次触发 INSTEAD OF 触发器的语句，该语句不会被递归调用，而是将该语句解析为对视图所依存的基本表进行的修改,再次触发的操作就像该视图没有 INSTEAD OF 触发器一样。由 UPDATE 更改的列必须解析到一个基表。对基表的每次修改都将应用约束并触发为该表定义的 AFTER 触发器。

为了查看这两次执行到底哪次是有用的，现在使用 UPDATE（尝试更新） 和 COLUMNS_UPDATED （实际更新）来跟踪查看。

创建2张表,跟踪列更改情况。尝试更新表(attemptOperation)和实际更新表(factOperation)：

--DROP TABLE attemptOperation,factOperationCREATE TABLE attemptOperation(Col VARCHAR(20),isUpdate BIT)CREATE TABLE factOperation(Col VARCHAR(20),isUpdate BIT)

更改视图,将对列的更新情况记录到表中,但不触发更新：

ALTER TRIGGER [dbo].[tgr_VTestTab_update]        ON [dbo].[VTestTab] INSTEAD OF UPDATE        AS BEGINIF(UPDATE([id]))INSERT INTO attemptOperation(Col,isUpdate) SELECT 'id',1IF(UPDATE([name]))INSERT INTO attemptOperation(Col,isUpdate) SELECT 'name',1IF(UPDATE([insertDate]))INSERT INTO attemptOperation(Col,isUpdate) SELECT 'insertDate',1IF(UPDATE([value]))INSERT INTO attemptOperation(Col,isUpdate) SELECT 'value',1IF(UPDATE([info]))INSERT INTO attemptOperation(Col,isUpdate) SELECT 'info',1IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&1=1)INSERT INTO factOperation(Col,isUpdate) SELECT 'id',1IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&2=2)INSERT INTO factOperation(Col,isUpdate) SELECT 'name',1IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&4=4)INSERT INTO factOperation(Col,isUpdate) SELECT 'insertDate',1IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&8=8)INSERT INTO factOperation(Col,isUpdate) SELECT 'value',1IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&16=16)INSERT INTO factOperation(Col,isUpdate) SELECT 'info',1ENDGO

再次对视图更新：

--再对视图更新UPDATE [VTestTab] SET [value]=10 WHERE id = 1

从执行计划看，视图是有更新了！，查看跟踪的表，也尝试更新并实际更新了！！~

再查看表中的记录，值却没有变化！~更新视图却没有反应到具体的表中！~

SELECT * FROM [VTestTab] WHEREid= 1

值没有变化是正常的，因为触发器里没有定义更新的语句，但是从跟踪来看，视图的确是有更改的！~

现在改回原来的触发器：

ALTER TRIGGER [dbo].[tgr_VTestTab_update]        ON [dbo].[VTestTab] INSTEAD OF UPDATE        AS  UPDATE [VTestTab] SET [name] = T2.[name],[insertDate] = T2.[insertDate],[value] = T2.[value],[info] = T2.[info]FROM [VTestTab] AS t1, inserted AS t2 WHERE t1.id = t2.idGO

--再对视图更新UPDATE [VTestTab] SET [value]=100 WHERE id = 1

发现是有两个执行计划，从前面可以知道，第一个执行计划是没有实际更新的，它更新的是视图，没反馈到表中，只有第二次在触发器内部定义的更新操作才有用！~

那么问题就来了：为什么视图的更改没有反应在具体表中？？难道微软所说的约束是在视图和表之间阻止了？（待解决）

 现在开始说下最初遇到的错误吧！~

--关联更新，有错误！~UPDATE t1 SET  t1.[value]=t2.[value] FROM [VTestTab] t1 join #TestTab t2 on t1.id=t2.id

消息 414，级别 16，状态 1，第 1 行
不允许使用 UPDATE，因为该语句会更新视图 "VTestTab"，而该视图参与联接并且有一个 INSTEAD OF UPDATE 触发器。

上面直接更新单个视图是正常的，唯有连接其他表更新时，才出现错误。

听说这个问题的出现，从07年已经提出了吧，只是一直没有被修复，不清楚 SqlServer 2014 修复了没有！~

有两种解决方法，但是性能不是很好！

--【方法一】UPDATE [VTestTab] SET  [value] = (SELECT [value] FROM #TestTab T WHERE [VTestTab].id=T.id) ,   [info] = (SELECT [info] FROM #TestTab T WHERE [VTestTab].id=T.id)WHERE EXISTS(SELECT * FROM #TestTab K WHERE [VTestTab].id=K.id)--【方法二】MERGE INTO [VTestTab] AS T1USING #TestTab AS T2 ON  T1.id=T2.idWHEN MATCHED THEN UPDATESET T1.[value] = T2.[value],T1.[info] = T2.[info];

参考：

设计 INSTEAD OF 触发器

通过视图修改数据

CREATE TRIGGER (Transact-SQL)

深入理解SQL Server 2005 中的 COLUMNS_UPDATED函数