Ms sql Trigger - Can Delete Trigger Affect Also Deleted Row Trigger

Question

I Have Table Name Called Widgets And There is Delete Trigger On it. When i Delete one specific Row I want Delete Also Other Rows Which Has Relotionship

Trigger Like

ALTER TRIGGER [dbo].[Trigger_sys_widgets_Delete]
   ON  [dbo].[sys_widgets]
   AFTER DELETE
AS 
BEGIN
    Delete From sys_Widgets Where parent in (Select id From deleted);
END

But This doesn't delete other Rows Just First Row Deleted !

Can i make This Trigger Works on Depete loop till Nothing Left in relative Childs

Also Try To Cascade Delete Like

Alter Table sys_widgets Add Constraint FK_sys_widgets foreign Key (parent) References sys_widgets(id) On Delete Cascade;

Is This Possible To Do That For Same Table

You use [cascade delete](https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) — Khalil, Aug 26 '17 at 09:33
but is There way to do that for same table Like Alter Table sys_widgets Add Constraint FK_sys_widgets foreign Key (parent) References sys_widgets(id) On Delete Cascade; — Proje, Aug 26 '17 at 09:50
On delete cascade for self-referencing table [link](https://stackoverflow.com/questions/42228082/on-delete-cascade-for-self-referencing-table) — Khalil, Aug 26 '17 at 09:53

Oleg Belousov · Answer 1 · 2017-08-26T10:29:20.970

You may use recursive CTE to delete all children by one trigger run.

ALTER TRIGGER [dbo].[Trigger_sys_widgets_Delete]
    ON  [dbo].[sys_widgets]
    AFTER DELETE
AS 
BEGIN
    WITH ForDelete AS
    (
        SELECT widgets.id
        FROM [dbo].[sys_widgets] AS widgets
        INNER JOIN deleted ON widgets.parent = deleted.id
        UNION ALL
        SELECT widgets.id
        FROM [dbo].[sys_widgets] AS widgets
        INNER JOIN ForDelete ON widgets.parent = ForDelete.id
    )
    DELETE FROM [dbo].[sys_widgets]
    WHERE id in (SELECT id FROM ForDelete);
END

Ms sql Trigger - Can Delete Trigger Affect Also Deleted Row Trigger

1 Answers1