Can a trigger on a table fire itself on the same table? 1

[webmigit]

I have a categories table, and it has a trigger on it. When a change is made to a category, the trigger fires a change on the parent category. By design, the change to the parent should fire a change to the parent of the parent, and so on and so forth... but its not.

Is there a way to make sql do this? I'm sure I'm probably encountering some sort of self-nesting protection so that you can't make a trigger fire itself over and over and over again by accident or maliciously.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ESquared]

Direct recursion is a database-level setting.

See the Recursive Triggers section of CREATE TRIGGER in BOL for an overview.

It mentions sp_dboption, something like (untested):

exec sp_dboption 'database', 'recursive triggers', 'true'

But I personally would avoid setting this option as it can cause some really strange and unexpected behavior. At the very least, review every existing trigger already in the database and confer with all other developers who have worked, are working, or will work on the database. Finally, make sure that your trigger uses correct IF UPDATE (Column) logic so no unnecessary updates are made.

Another option is to do the recursion yourself with a temp table. Insert the rows you want to work with into the temp table. Begin a while loop which runs as long as there are rows. In the while loop, do the work on the rows in the table, then replace the table's contents with the parent items.

Even better, insert the rows you need to work with into a temp table. In a loop, do a join to add any missing parent rows until no more are added. Then do your data update in a single step. This is, in my opinion, the best answer of all. A single update is less expensive than multiple updates, and you don't have to mess around with the potentially dangerous direct recursion option.

Erik

PS The direct recursion option is dangerous because you can end up in an infinite loop (and code that previously worked can run into this problem) which will auto-rollback data updates because the maximum nesting level will be quickly reached. This is the same effect as having a table that can't be updated. Can you imagine the kind of problems that could cause?

 

[webmigit]

Nice. Three approaches. Thanks a lot.

 

[ESquared]

Another option is to use a nested sets model for your hierarchy/tree, which allows you to get all parents or children of selected nodes in a single query... but that would take a redesign of your database which I'm sure you don't want to do.