February 16, 2012 at 11:28 am
Hello,
I am trying to figure out how to create a trigger on Table1 that will , upon insert of a row into Table1 - do the following:
1.) Take the Table1.Vendor_Category and lookup all the VendorIDs with that Vendor_Category from Table2.
2.) Insert rows into Table1 using the list of VendorIDs returned from Table2 - creating new records in Table1 for (VendorID, Vendor_Category, Inserted Item_Number, Inserted Vendor_Part_Number, and Inserted Vendor_Part_Description).
Table1 has the following fields
- VendorID
- Vendor_Category
- Item_Number
- Vendor_Part_Number
- Vendor_Part_Description
Table2 (Vendor Master)
- VendorID
- Vendor_Category
I have been racking my brain on how to construct this and am not sure how to go about it.
Thank you for your help.
Greg
February 16, 2012 at 11:41 am
I guess I'm not clear on why that would be a trigger. What would trigger it? Why would you want it to run every time you insert/update/delete?
- 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
February 16, 2012 at 1:28 pm
I think a bit more clarity of the scenario is required. What you have described is an infinite loop.
...paraphrased from your description...
I want to create an insert trigger on table1 that will insert data into table1.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2012 at 1:36 pm
Oh I am beginning to now see the real issue. Your data is not normalized and you are trying to figure out to make it work with a poor data design.
You have VendorID and Vendor_Category in both tables. Unless that is a composite key this is an issue. I suspect it is not a composite key. Your Vendor_Category table has Item_Number. I will assume Item_Number is a sku.
It seems that what you are trying to achieve is to allow items to belong to more than 1 category? If I am correct I think you need to restructure your data instead of coming up with some sort of trigger scheme as a work around.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2012 at 2:00 pm
This would be triggered by an insert into Table1. Then lookup the list to then insert multiple records back into Table1. As I am typing this I realize that it may create a loop.
How would you suggest I do this?
Thanks,
Greg
February 16, 2012 at 3:08 pm
GP_NC (2/16/2012)
This would be triggered by an insert into Table1. Then lookup the list to then insert multiple records back into Table1. As I am typing this I realize that it may create a loop.How would you suggest I do this?
Thanks,
Greg
I would suggest normalizing your data and then this is not needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2012 at 6:43 am
GP_NC (2/16/2012)
This would be triggered by an insert into Table1. Then lookup the list to then insert multiple records back into Table1. As I am typing this I realize that it may create a loop.How would you suggest I do this?
Thanks,
Greg
Don't bother. If you want one row in Table1 per item ID, per row in Table2, just do a join between the two in a view, with the right join criteria, and you'll have a dataset that does what you need. No need to persist it, from what's been described so far.
- 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
February 17, 2012 at 11:07 am
Sorry, I guess I did not explain enough to get a clear picture of my intentions. Say the following record gets inserted into Table1:
Table 1
VendorID: V001
Vendor_Category: Max1
Item_Number: IV1001
Vendor_Part_Number: VITEM001
Vendor_Part_Description: Bolt
AND
Table2 has the following data in it:
VendorIDVendor Category
V001Max1
V002Max1
V003Max2
V004Max1
V005Max2
V006Max1
When the record above is inserted into Table1 – I need something automated that will look at the Vendor_Category of that record (Max1), then select all the Vendors in Table2 with Vendor_Category = Max1 and create new records in Table 1 for those vendors with Max1 as their Vendor_Category as follows:
VendorID Vendor_Category Item_NumberVendor_Part_Number Vendor_Part_Description
V001Max1IV1001VITEM001Bolt
V002Max1IV1001VITEM001Bolt
V004Max1IV1001VITEM001Bolt
V006Max1IV1001VITEM001Bolt
I realize the tables are not properly normalized but I described it like this to show the common fields only.
Thanks again,
Greg
February 19, 2012 at 1:35 pm
I do hope the last post gave a clearer picture. If someone can just tell me if using a trigger is the best way to handle this situation. I am desperate for ideas and help...
Thanks
February 19, 2012 at 2:55 pm
GP_NC (2/19/2012)
I do hope the last post gave a clearer picture. If someone can just tell me if using a trigger is the best way to handle this situation. I am desperate for ideas and help...Thanks
No a trigger is not the best way to go here. The best way to go is to fix the underlying cause of the situation, normalize your data. I know this is not always possible but should be seriously considered.
You really shouldn't create an insert trigger on a table that is going to insert data into itself. You will have created a possible infinite loop and mountains of headaches to maintain this. In fact, I think it would be an infinite loop because unless your trigger disabled itself for its inserts it would just keep going round and round until max recursion is hit and the process blows up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 6:43 am
Break the tables up a bit more (normalize them).
You should have a table of vendors, a table of categories, and a table of items per category. Then, if a vendor is in Category1, all the items (bolts or whatever) in Category1 will be listed under that vendor.
- 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 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply