SqlServer INSTEAD OF UPDATE 視圖觸發器問題，sqlserverinstead

SqlServer INSTEAD OF UPDATE 視圖觸發器問題，sqlserverinstead

源於系統中的 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函數