无法在 View SQL Server 2005 上执行删除 [英] Unable to perform delete on View SQL Server 2005

问题描述

我无法对视图执行删除操作.在各个表上一切正常.

I am unable to perform a delete on a View. Everything worked fine on the individual tables.

添加触发器

CREATE TRIGGER myTrigger
ON [ViewName]
INSTEAD OF DELETE
AS
DELETE
FROM [ViewName]
WHERE [ColumnName] < DATEADD(Day, -90, GETDATE())

在添加触发器之前我收到以下错误

I got the following error before adding a trigger

View or Function "blah" is not updateable because the modification affects multiple base tables>

推荐答案

好的，让我们想象一个会发生此错误的实例(因为您还没有显示您的视图定义).

Okay, let's imagine one instance where this error will occur (since you haven't shown your view definition).

假设我们有一个视图:

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)

我们会收到您显示的错误(或类似错误).所以我们需要的是一个触发器:

we'll get the error you've shown (or similar). So what we need is a trigger:

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

This trigger gets considerably more complex to write if there's no easy way to correlate rows from the deleted psuedo-table with which rows need to be deleted from each base table.

这篇关于无法在 View SQL Server 2005 上执行删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！