Elsayed Elsayed - 20 days ago 5
SQL Question

Trigger to delete rows from table when delete duplicate rows from another related table

I 'am using sqlserver ,I created Trigger to delete from YTable when deleting duplicate rows from Xtable.
Note : YTable has a foreign key from XTable as On_ID

Trigger Statement

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.'


delete dublicates rows statment from 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


The Result of executing the preceding


(0 row(s) affected) AFTER DELETE TRIGGER fired.

(1271060 row(s) affected)


please what's the wrong of these statements

thank you very much

Answer

Statement

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)