Recursive AFTER INSERT trigger: is it possible ?
-
Hi,
We have a table that is setup as a Tree, meaning that a row can be the Parent row for other rows in the same table.
What we want to do is when a certain type of object is inserted into that table, we want to recursively insert the corresponding child rows as well. For example, if the inserted top row is what we call a KIT, we want to also insert all the components that form the kit. If it should happen that one such component is itself a kit, we want to insert its components as well (and so on, and so on).
Our first idea was to do this with a trigger, but to my surprise, it seems that MS SQL 2005 won't allow nested insert triggers.
What I find is that the trigger is fired for the first level row, inserting child components for it, but it won't fire deeper than that.
So the question is: Does MS SQL 2005 allow such a recursive use of AFTER INSERT triggers ?
Thank you
Luc Morin, T.P.
http://www.stlm.ca -
recursive triggers is a per-database option; it is usually rare that you really need to call a trigger recursively, so it's disabled by default.
right click...properties for the database in quesiton...Options...
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Wooot....
Thanks a lot !
Luc Morin, T.P.
http://www.stlm.ca -
Be cautious that you don't run into issues with other triggers.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
My question is: why you don't just generate the entire set of rows to be inserted along with the KIT and then insert them without launching the trigger over and over?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Bob Hovious (8/14/2009)
My question is: why you don't just generate the entire set of rows to be inserted along with the KIT and then insert them without launching the trigger over and over?Hi Bob.
The reason is that in the application, I don't have the full set of rows loaded, only those that are at the topmost level.
This is to not "load" the OrderDetail grid too much for the user. That way, the user only sees what he actually chose, that is the topmost item, and not all the children items.
Now, the reason we do that is that we want to generate ProductionOrders for any OrderDetail that has a certain property set, and again, we use a trigger on the OrderDetail table to insert/update/delete the ProductionOrders (1:1 relation between OrderDetail:ProductionOrder, the FK (nullable) being on ProductionOrder).
Is this crappy design ? Should I better handle everything in the application code ?
Thank you.
Luc Morin, T.P.
http://www.stlm.ca -
I'm talking about a query in the trigger itself. I understand that your user should only see/select "KIT" and then everything else happens under the covers. It just seem to me that the trigger should see that "KIT" was inserted, then insert additional rows for ALL the components that make up "KIT" without triggering again.
You could create a recursive CTE to chase down all the child rows and insert the result into your target table.
;with cte as (recursive chase pointers)
insert into targetTable
select * from cte
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Bob Hovious (8/14/2009)
You could create a recursive CTE to chase down all the child rows and insert the result into your target table.;with cte as (recursive chase pointers)
insert into targetTable
select * from cte
Bob,
You are right. For some reason the trees kept me from seeing the forest 😀
In other triggers (mostly deletes), I do use CTEs for recursion. I don't know why I didn't think of it in this case.
Thanks for helping me pull my head out of my a** :hehe:
Regards.
Luc Morin, T.P.
http://www.stlm.ca
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply