我无法对视图执行删除。各个桌子上的一切都运行良好。
EDIT1:添加触发器
CREATE TRIGGER myTrigger
ON [ViewName]
INSTEAD OF DELETE
AS
DELETE
FROM [ViewName]
WHERE [ColumnName] < DATEADD(Day, -90, GETDATE())
在添加触发器之前我收到以下错误
View or Function "blah" is not updateable because the modification affects multiple base tables>
好吧,让我们想象一下会发生此错误的一个实例(因为您没有显示视图定义)。
假设我们有一个视图:
CREATE VIEW dbo.V1
with schemabinding
as
select 'T1' as TabName,T1ID as ID,ImportantDate from dbo.T1
union all
select 'T2',T2ID,ImportantDate from dbo.T2
我们现在尝试的是:
DELETE from dbo.V1 where ImportantDate < DATEADD(day,-90,CURRENT_TIMESTAMP)
我们会收到您所显示的错误(或类似错误)。所以我们需要一个触发器:
CREATE TRIGGER T_V1_D
on dbo.V1
instead of delete
as
set nocount on
delete from dbo.T1 where T1ID in (select ID from deleted where TabName = 'T1')
delete from dbo.T2 where T2ID in (select ID from deleted where TabName = 'T2')
如果没有简单的方法来关联来自deleted
需要从每个基表中删除行的伪表。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)