Eugene Elutin (9/18/2012)
CELKO (9/18/2012)
I have a product_sku CHAR(10) s table and a storage product_sku CHAR(10) s table.
No, you do not. A table has a key and you have none. What you have are two decks of punch cards written in SQL. You have no DRI or other constraints...
He has. At least he has what is called keyless table. Have you ever heard of it? Google it! Yes it is normally undesirable for a number of reasons, but it is still a table. You can create one and it will be there, just try! It doesn't look like two decks of punch cards to me.
Did you ever consider the possibility that OP very often do post very simple representation of what they really have?
That's exactly what I did... my products' table has over 100 fields and so does the storage one... It would be complete waste of time to post it all here for the question's purpose.
Eugene Elutin (9/18/2012)
CELKO (9/18/2012)
...We would never store a total we can compute in the products table. You would have to fix this design flaw with triggers!
...
You are wrong here. We do store "totals" in the tables (and not only in Data warehouse and Reporting solutions, where it is very common). It all depends on requirement. Do you know them? How can you judge?
If the product's storage table has over 1.000.000 rows, cause there are over 100 storage and 10.000 products (it's a kind of Walmart company, with many products and stores) I believe it's better do "denormalize" the table and put a total column on the product's table. There are few insert and updates but lot's of list so it's best this way...
This is some code I just "inherited" and have to analyse and improve... that's why I posted these questions, I'd never build a trigger with cursors to call a another SP... unless it's the only way to do it.
But in this case since the the 2nd SP is called from other SPs, not only by the trigger, it was design like this to do some code reuse, but I believe it's best not to do it...
Thanks,
Pedro