无法在View SQL Server 2005上执行删除

Question

我无法在视图上执行删除。各个表上的一切工作正常。

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>

Answer 1

好的，我们假设一个实例将发生此错误（因为您没有显示视图定义）。

假设我们有一个观点：

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伪表中的行与需要从每个基本表中删除的行相关联，则此触发器的编写将变得更加复杂。