SQL Server INSTEAD OF UPDATE view trigger problem, sqlserverinstead

SQL Server INSTEAD OF UPDATE view trigger problem, sqlserverinstead

An error occurs when the system's instead of update view trigger is associated with table UPDATE:

Message 414, level 16, state 1, 1st rows
UPDATE is not allowed, because this statement updates the view "VTestTab", and this view participates in the join and has an instead of update trigger.

So what is the execution principle of the trigger !~

Note:

A view can only be referenced by an instead of trigger. Each INSERT, UPDATE, or DELETE statement can define a maximum OF one instead of trigger.
The instead of trigger cannot be used to use the updatable view with check option. Otherwise, SQL Server will cause an error.

Create a test environment:

-- 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 Create a view update trigger (mainly for this 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 to the view 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 -- create another table (or entity table) and later use it to associate and update SELECT * INTO # TestTab FROM [VTestTab] -- current table, view, and temporary table SELECT [id]. [name], [insertDate], [value], [info] FROM [dbo]. [TestTab] SELECT * FROM [dbo]. [VTestTab] SELECT * FROM [dbo]. # TestTab

Update a single view:

-- UPDATE view [VTestTab] SET [value] = 100 WHERE id = 1

Update a single view. The result is normal, but it is executed twice !~ You can see the execution plan !~

Is it true that it was executed twice ?!

Official Instructions:
If a view-defined instead of trigger executes a statement that will trigger the instead of trigger again, the statement will not be recursively called, INSTEAD, the statement is parsed to modify the basic table that the view depends on. The re-trigger operation is like that the view does not have an instead of trigger. Columns changed by update must be parsed to a base table. Each modification to the base table applies the constraint and triggers the AFTER trigger defined for the table.

To see which of the two executions is useful, use UPDATE (UPDATE attempt) and COLUMNS_UPDATED (actual UPDATE) to track and view the results.

Create two tables to track column changes. Try to update Tables (attemptOperation) and actually update Tables (factOperation ):

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

Change the view to record updates to the column in the table, but do not trigger 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)&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 the view again:

-- UPDATE [VTestTab] SET [value] = 10 WHERE id = 1 for the view.

From the execution plan, the view is updated !, View the tracked table, and try to update and actually update it !!~

Check the records in the table, but the value does not change !~ Updating the view does not reflect the specific table !~

SELECT * FROM [VTestTab] WHEREid = 1

It is normal that the value is not changed because no updated statement is defined in the trigger, but from the trace point of view, the view is indeed changed !~

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

-- UPDATE [VTestTab] SET [value] = 100 WHERE id = 1 for the view

We found that there are two execution plans. We can see from the previous figure that the first execution plan is not actually updated. It updates the view and does not provide feedback to the table, this operation is only useful for the second update operation defined inside the trigger! ~

The question is: Why is the view change not reflected in a specific table ?? Does Microsoft block the constraints between views and tables? (To be resolved)

 Let's start with the first error !~

-- Associated update. Error !~ UPDATE t1 SET t1. [value] = t2. [value] FROM [VTestTab] t1 join # TestTab t2 on t1.id = t2.id

Message 414, level 16, state 1, 1st rows
UPDATE is not allowed, because this statement updates the view "VTestTab", and this view participates in the join and has an instead of update trigger.

It is normal to directly update a single view above. An error occurs only when other tables are connected for update.

I heard that this problem has been raised since 2014, but it has not been fixed. It is unclear if SqlServer has been fixed !~

There are two solutions, but the performance is not good!

-- [Method 1] 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) -- [method 2] merge into [VTestTab] AS T1USING # TestTab AS T2 ON T1.id = T2.idWHEN matched then updateset T1. [value] = T2. [value], t1. [info] = T2. [info];

Refer:

Design the INSTEAD trigger

Modify data through views

Create trigger (Transact-SQL)

An in-depth understanding of the COLUMNS_UPDATED function in SQL Server 2005