SQL Server INSTEAD of UPDATE view trigger issues

An error occurred when the INSTEAD of Update View Trigger association table update originated in the system is as follows:

MSG 414, Level 16, State 1, line 1th
Update is not allowed because the statement updates the view "Vtesttab" and the view participates in the join and has a INSTEAD of UPDATE trigger.

So test how the trigger is executed! ~

Description

Views can only be referenced by INSTEAD of triggers, and each INSERT, UPDATE, or DELETE statement can define a maximum of INSTEAD of triggers
INSTEAD of triggers cannot be used for updatable views that use with CHECK OPTION, or SQL Server throws an error

To create a test environment:

--drop table [testtab]--truncate table] [testtab]--CREATE TABLE [dbo]. [Testtab] ([ID] [int] not null,[name] [varchar] (no null,[insertdate] [datetime] not null,[value] [numeric] (4) null,[info] [ VARCHAR] (null,constraint) [Pk_testtab] PRIMARY KEY CLUSTERED ([id] ASC)) on [primary]go--creates view CREATE VIEW [dbo]. [Vtesttab] Asselect [id], [name],[insertdate],[value],[info]from [dbo]. [Testtab] go--Create a View update trigger (mainly for this raised issue!) 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 data into view INSERT into [Vtesttab]select 1, ' KK ', GETDATE (), 0, ' Unio N allselect 2, ' JJ ', GETDATE (), 5, ' HH ' UNION allselect 3, ' SS ', GETDATE (), ten, ' UNION allselect 4, ' MM ', GETDATE (), 0, Nullunion allselect 5, ' YY ', GETDATE (), one, ' go--create another table (or entity table), later used for association update SELECT * into #TestTab from [Vtesttab]--current table, view, Temp table SELECT [Id],[name],[Insertdate],[value],[info] FROM [dbo]. [Testtab] SELECT * FROM [dbo]. [Vtesttab] SELECT * FROM [dbo]. #TestTab

now update to a single view:

--Update [Vtesttab] SET [value]=100 WHERE id = 1] on view

Update to a single view, the result is normal, but executed 2 times!~ execution plan can be seen! ~

Did it really run two times?!

The official notes are:
If the INSTEAD of trigger defined for the view executes a statement that typically triggers the INSTEAD of trigger again, the statement is not recursive, but instead resolves the statement to a modification of the base table on which the view is dependent, and the operation is triggered again as if the views were not INSTEAD of Trigger-like. The column changed by UPDATE must resolve to a base table. Each modification to the base table will apply the constraint and trigger the AFTER trigger defined for the table.

To see exactly which of these two executions are useful, now use update (try to update) and columns_updated (actual update) to track the view.

Create 2 tables to keep track of column changes. Try to update the table (attemptoperation) and the actual Update table (factoperation):

--drop Table Attemptoperation,factoperationcreate Table Attemptoperation (Col VARCHAR (), isupdate BIT) CREATE table Factoperation (Col VARCHAR), Isupdate BIT)

Change the view to record updates to the column in the table without triggering the update:

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) insert into factoperation (col,isupdate) SELECT ' id ', 1IF (SUBSTRING (columns_updated (), +) &2=2) insert INTO Factoperation (col,isupdate) SELECT ' name ', 1IF (SUBSTRING (columns_updated (), +) &4=4) INSERT into Factoperation ( col,isupdate) SELECT ' insertdate ', 1IF (SUBSTRING (columns_updated (), +) &8=8) INSERT into Factoperation (Col, isupdate) SELECT ' value ', 1IF (SUBSTRING (columns_updated (), &16=16) INSERT into Factoperation (col,isupdate) SELECT ' Info ', 1ENDGO

Update the View again:

--Updates to the view update [Vtesttab] SET [value]=10 WHERE id = 1

From the execution plan, the view is updated! , look at the tracked table, also try to update and actually UPDATE!! ~

Then look at the records in the table, but the values don't change! ~ Update view is not reflected in the specific table! ~

SELECT * from [Vtesttab] WHERE ID = 1

It is normal that the value does not change because there are no updated statements defined in the trigger, but the view is actually changed from the trace! ~

Now change back to the original trigger:

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

--Updates to the view update [Vtesttab] SET [value]=100 WHERE id = 1

Discovery is there are two execution plans, from the front can be known that the first execution plan is not actually updated, it updates the view, no feedback into the table, only the second time within the trigger definition of the update operation is useful! ~

So the question is: Why does the view change not respond to a specific table?? Did Microsoft say that the constraint was blocked between the view and the table? (pending resolution)

Now let's start with the mistakes you first encountered! ~

--associated updates, with errors! ~update T1 SET  t1.[ Value]=t2. [Value] From [Vtesttab] T1 join #TestTab T2 on T1.id=t2.id

msg 414, Level 16, State 1, line 1th
Update is not allowed because the statement updates the view "Vtesttab" and the view participates in the join and has a INSTEAD of UPDATE trigger.

It is normal to update a single view directly above, and only an error occurs when you connect to another table update.

Heard that the emergence of this problem, from 07 has been proposed bar, but has not been repaired, not clear SQL Server 2014 fixed no! ~

There are two workarounds, but the performance is not very good!

--"Method One" UPDATE [Vtesttab] SET  [value] = (SELECT [value] from #TestTab T WHERE [vtesttab].id=t.id),   [INFO] = (Selec t [info] from #TestTab t where [vtesttab].id=t.id] where EXISTS (SELECT * from #TestTab K WHERE [vtesttab].id=k.id)--"Method two" ME RGE to [Vtesttab] as t1using #TestTab as T2 on  T1.id=t2.idwhen matched then Updateset t1.[ Value] = T2. [Value],t1. [INFO] = T2. [INFO];

Reference:

Design INSTEAD of Triggers

Modifying data through a view

CREATE TRIGGER (Transact-SQL)

Deep understanding of columns_updated Functions in SQL Server 2005

SQL Server INSTEAD of UPDATE view trigger issues