I have this table (Account) containing fields ID and ParentID. ParentID's value is the ID of the parent account for that account. Now, I set up a trigger that on delete of the parent, all children
also gets deleted. But the delete only works one level. I mean when I delete the parent, the direct children gets deleted but the children of those chidlren does not get deleted. Here is my trigger:
CREATE TRIGGER [Base].[Account_DeleteChildAccounts] ON [Base].[Account]
AFTER DELETE
AS
BEGIN
DELETE FROM [Base].[Account]
WHERE ParentID IN (SELECT ID FROM Deleted)
END
I thought, when the parent deletes its children via the trigger, the trigger would again fire and delete its chidlren. How would I go about doing this? And is there a difference in AFTER DELETE and FOR DELETE? Please help.