June 9, 2009 at 7:05 am
I am hoping to get some feedback about a method I am using to update a BoM. My db has a pretty standard Bill of Material structure, one table has the basic structure of ID, ParentPartFK, ChildPartFK. To get a list of parts I use a CTE which works fine. When a part is added to a Job it is denormalized a bit so the Job table looks like this:
Job ID
Part FK
BomNumber
So a sample entry would look like this:
1 1 1
2 3 1.1
3 7 1.2
4 8 1.2.2
In this case the user just added the part with the Part FK of 1. A trigger on the Job table then goes to the Bill of Material table and gets all the children and adds them as well. As you can see I need to have the trigger act recursively, but my common sense (and any info I was able to find) indicates that recursive triggers can be dangerous. You need to evaluate every trigger you have (or add) for unintended consequences. My semi-solution is to enable recursive triggers, run the update sp, then disable recursive triggers. I realize that there are several holes in this practice, but with less than 25 users and light updating (<100 a day) I don't anticipate running into a problem. It has been working like this with no issues for two years, but has nagged me since I implemented it. Does anyone have a better way to do this? Please note that there are an undetermined number of levels to the parts, could be up to 20. I also check for and prevent a self-reference endless loop (a part includes itself as a child).
TIA,
Peter Lake
June 9, 2009 at 8:57 am
I'm having trouble visualizing your data structure from the sample you gave. For one thing, what's a "Job" in this case? How does it relate to a "Part FK"?
The basic danger of recursive triggers is that you don't know when they'll end. Even if they don't loop forever, they can keep running for quite a while, and your transaction can't commit till all the recursions are done. That can do messy things to the transaction log, table locks, etc.
As an aside, I'd seriously look into nested sets hierarchies for a BoM structure. Google/Bing/Yahoo/whatever "Joe Celko nested sets", or take a look at my article on the subject http://www.sqlservercentral.com/articles/T-SQL/65540/.
A well-put-together nested sets hierarchy for your BoM is most likely going to get rid of your need for this trigger altogether, and will improve performance tremendously.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 9:35 am
Thanks for the reply.
Believe it or not, before I created this table structure and update routine I did read your article (quite informative, really good ideas) and some of Joe Celkos stuff on different sites, as well as other authors stuff here on SSC. I know I just gave a brief explanation of my structure, but I just wanted some feedback on turning recursion on and off to see if there were ramifications that I hadn't considered and handled.
I will lay out my structure and show what I believe is the different characteristic that makes it not directly fit any of the examples you show or mention.
PartMaster table (contains static info like size, material, etc.)
PartHistory table (contains fk to PM table and dynamic info like source, location, etc. New rev added each time info changed.)
PBOM table (Parent/Child relationship, very similar to your Hierarchy example. Each row has ID, ParentFK, ChildFK, Row, Qty.)
So the PBOM table contains the current Bill for any Part and is updated with no history kept anytime the Bill changes.
When a part is added to a Job (Job would physically be the piece we are making for a client) the current Bill is copied into the JBOM table.
JBOM table (ID, JobFK, PartHistoryFK, BOMNUM). As you can see the JBOM table is 'flattened out'. It is done like this because we need to know the configuration of the Parts as they shipped on the Job, regardless of changes in the Bill later. In this case I use the BOMNUM to track the hierarchy, so a part with a BOMNUM of 1.2.2 would be a component of the part with BOMNUM 1.2 which in turn is a component of BOMNUM 1.
Getting the parts from the Hierarchy table (PBOM) into the Flattened table (JBOM) is where I use the recursive trigger. The user simply adds the top level part and the trigger navigates PBOM adding the parts with the appropriate BOMNUM into the JBOM table.
I realize I could have just used a copy of the PBOM with a link to a JBOM with just parents, but that would mean a lot more complicated T-SQL for reporting and GUI use.
I hope I was clear about what I perceived as differences from the examples you referred to, thanks for reading anyway. I have gotten much good info from reading your posts on this site, thanks for your time.
June 9, 2009 at 9:52 am
Peter Lake (6/9/2009)
...In this case the user just added the part with the Part FK of 1. A trigger on the Job table then goes to the Bill of Material table and gets all the children and adds them as well. As you can see I need to have the trigger act recursively...
Why can't you just use a recursive CTE to get all of the children and add them in? I can't see why you would need a recursive trigger for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2009 at 12:04 pm
Perhaps I can. I use one to show the Bill to the user. I would need to massage that one to enable the BOMNUM creation I talked about earlier, but for some reason this idea didn't occur to me. I will investigate.
Thanks for the idea.
June 9, 2009 at 12:38 pm
After reading your description, I'd go with what Barry suggested. I'd also suggest moving it from a trigger into the insert proc, if that's possible. Makes for much easier code maintenance.
Thank you for your compliment on my writings. I appreciate it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply