更新时间:2023-02-03 07:40:51
好的,让我们想象一个会发生此错误的实例(因为您还没有显示您的视图定义).
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.