Help in delete trigger

[gnobber]

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.

[sknake]

You don't want to do this with a trigger, and you probably don't want to do this altogether.

Deletes like that should be done using PK/FK constraints and enabling cascading deletes. Take a look here to set that up:
http://www.google.com/search?hl=en&source=hp&q=MSSQL+Cascading+delete&aq=f&oq=&aqi=g-sx1g1

Why you don't want to do that with a trigger:
There is a maximum number of rows that a trigger can affect. If it exceeds the maximum the value then it blows up and rolls back the transaction.

Once I wanted to update a column on INSERT/UPDATE and I launched a massive update query when a value changed and the system broke over time because when the trigger started to affect to many records it would throw an exception.

[gnobber]

Hi Scott,

Really I agree with you. I also do not want to use a trigger and instead just relate the ID to the ParentID but the problem is when I relate the ID to the ParentID (by creating a new Database Diagram in Management Studio; I use this when relating because I am not yet familiar enough with T-SQL), the Delete and Update rule in the Insert And Update Specification is disabled. This only happens when Im using this setup (where ID and ParentID in same table). I've asked about this behavior in other forums and said that it wasn't possible in SQL Server because it would create a circular reference according to MSDN so that is why I resorted to using a trigger. Any ideas?

[sknake]

You should handle this task application-side. I don't know what language you're interfacing the database with but create a "business object" class which handles all of your SQL-Data-Access so you can ensure the queries are ran properly.

[gnobber]

Hi again Scott,

Someone has mentioned to me something about "recursive CTE" (which I do not know about). So im going to read about it first. But if that won't work for me, I think I might go with what you just suggested. In the meantime, I'll leave this thread as unsolved and will get back to it after I have read about this recursive CTE. Thanks for the suggestion!

[gnobber]

Well, I ended up handling it in my application code. Anyway, it works now.