I 'am using sqlserver ,I created Trigger to delete from YTable, when I deleted duplicate rows from Xtable.
Note : YTable has a foreign key from XTable as On_ID
CREATE TRIGGER trgAfterDeletev4 ON XTable
declare @empid int;
select @empid=d.[On_ID] from deleted d;
Delete from YTable
where [On_ID]= @empid
PRINT 'AFTER DELETE TRIGGER fired.'
WITH C AS(
RN = ROW_NUMBER()OVER(PARTITION BY [col1] ORDER BY [col2])
DELETE FROM C WHERE RN > 1
(0 row(s) affected) AFTER DELETE TRIGGER fired.
(1271060 row(s) affected)
select @empid=d.[On_ID] from deleted d
will not work correctly in the case when you're deleting more then one record from
XTable since you can't place multiple IDs into single integer variable.
You can simply change your delete statement in trigger to
Delete from YTable where [On_ID] in (select [On_ID] from deleted)