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]
AS
SELECT [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.id
GO

--	插入数据到视图
INSERT INTO [VTestTab]
SELECT 1,'kk',GETDATE(),0,''
UNION ALL
SELECT 2,'JJ',GETDATE(),5,'HH'
UNION ALL
SELECT 3,'SS',GETDATE(),10,''
UNION ALL
SELECT 4,'MM',GETDATE(),0,NULL
UNION ALL
SELECT 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,factOperation
CREATE 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 
BEGIN
IF(UPDATE([id]))
	INSERT INTO attemptOperation(Col,isUpdate) SELECT 'id',1
IF(UPDATE([name]))
	INSERT INTO attemptOperation(Col,isUpdate) SELECT 'name',1
IF(UPDATE([insertDate]))
	INSERT INTO attemptOperation(Col,isUpdate) SELECT 'insertDate',1
IF(UPDATE([value]))
	INSERT INTO attemptOperation(Col,isUpdate) SELECT 'value',1
IF(UPDATE([info]))
	INSERT INTO attemptOperation(Col,isUpdate) SELECT 'info',1

IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&1=1)
	INSERT INTO factOperation(Col,isUpdate) SELECT 'id',1
IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&2=2)
	INSERT INTO factOperation(Col,isUpdate) SELECT 'name',1
IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&4=4)
	INSERT INTO factOperation(Col,isUpdate) SELECT 'insertDate',1
IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&8=8)
	INSERT INTO factOperation(Col,isUpdate) SELECT 'value',1
IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&16=16)
	INSERT INTO factOperation(Col,isUpdate) SELECT 'info',1
END
GO

再次对视图更新:

--	再对视图更新
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.id
GO

--	再对视图更新
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 T1
USING #TestTab AS T2 ON  T1.id=T2.id
WHEN MATCHED THEN UPDATE
SET T1.[value] = T2.[value],T1.[info] = T2.[info];



参考:

设计 INSTEAD OF 触发器

通过视图修改数据

CREATE TRIGGER (Transact-SQL)

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


评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值