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?
...
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?
...
We hate triggers and procedural code in SQL.
...
May I ask who is "We"? Do your call yourself in a plural form? Actually, looks like you constantly do this...
I don't hate triggers and procedural code. I do use triggers and procedural code in SQL where appropriate. Other SQL Experts I know, usually do the same - use any SQL Server "tool" where it is appropriate - they don't usually hate them.
...
Your second procedure is not needed at all. The trigger is also a waste of code and time. SQL programmers put this kind of computation in a VIEW where it is always current and invoked only when needed.
...
It all depends!
Some time yes, SQL Developers put this sort of things into VIEW, sometimes we use COMPUTED column and some times we do persist it in a table.
It all depends!
Have you tried to execute your posted code, or "We" don't care? 😀