当从另一个相关表中删除重复行时,创建触发器以从表中删除
问题描述:
我正在使用sqlserver,当我从Xtable删除重复行时,我创建了触发器以从YTable中删除。 注:YTable具有XTable一个外键On_ID当从另一个相关表中删除重复行时,创建触发器以从表中删除
触发查询
CREATE TRIGGER trgAfterDeletev4 ON XTable
AFTER DELETE
AS
declare @empid int;
select @empid=d.[On_ID] from deleted d;
Delete from YTable
where [On_ID]= @empid
PRINT 'AFTER DELETE TRIGGER fired.'
从XTable
WITH C AS(
SELECT *,
RN = ROW_NUMBER()OVER(PARTITION BY [col1] ORDER BY [col2])
FROM [Sourcing].[src].XTable
)
DELETE FROM C WHERE RN > 1
结果中删除重复的行
(0行(S )受到影响)AFTER DELETE TRIGGER开枪。
(1271060行(S)的影响)
请有什么不对这些查询的
非常感谢你
答
声明
select @empid=d.[On_ID] from deleted d
将无法正常工作当你从XTable
删除多于一条记录的情况,因为你不能放置多个ID转换为单个整数变量。
你可以简单地改变触发你的DELETE语句
Delete from YTable
where [On_ID] in (select [On_ID] from deleted)
它的工作好谢谢@Andy科尔涅耶夫 – Elsayed