We have a few issues that we hope to get some information on as follows. We are not sql gurus and are learning on the fly (not the best, but we have to deal with this on a day by day basis while we interview SQL dbs).

A) We have a DB that looks like this, we inherited this from a company that we just bought, so we need to keep this intact until we move to another application development platform and redesign the DB,

B) Here is the partial schema:

A) DB: Customer_Activities (there are more tables etc, but I think this is the basics that would be needed to understand the problem):
B) Customers: customerno (PK), purchase_no (idx), total_purchase, no_purchases (int), other columns
C) Purchases: purchase_no (pk), customerno (idx-dupes), item_qty (int), item_cost (float), item_total (float) other columns,

relationship looks like this: customers -> purchases (1:many) on customerno.

Our current VB app performs the following:

1) when a new customer is added, the total_purchases is set to 0.00, no_purchases is set to 0 (zero),
2) when a customer makes a purchase, the following is performed "in the app",
   a) item_total is calculated as item_qty * item_cost,
   b) item_total is added to [customers].[total_purchases],
   c) item_qty is added to [customers].[no_purchases],

As we are looking at moving to a different dev platform (such as vb.net or Delphi etc), we would like to have sql2k5 perform these calculations via triggers. I personally don't think the app should be doing this anyway. :)

So we would like to have a trigger(s) on purchases that does the following (there would never be more than on record inserted/updated or deleted at a time):

i) adds the value from 2-b above to the total_purchase in Customers,
ii) adds the value from 2-c above to no_purchases in Customers,
iii) if a value in purchases.item_qty or item_cost is changed, this needs to be recalculated in the trigger (as an update I would suspect). Then this would have to be reflected in customers.total_purchases and no_purchases.

Another thing this app doesn't take into account is if a customers purchase is deleted, then there are inaccurate totals left in the appropriate customers columns (total_purchases and no_purchases). We think that the out-going db was doing this via his query tool or something similar (he's not cooperative in a few areas) and documentation is sparse if not incomplete. Very frustrating.

We've tried several different triggers and have the following problems and just can't seem to get the result we need. We do have a few triggers that update total_purchases (when added), but nothing for deletion or change.

I do realize that this is a lot to ask, and as mentioned above the whole thing needs a redesign (which hopefully a new SQL db can do for us), but we need something for the interim.

I've spent the better part of 2 days scouring the web and have made some progress, but alas not nearly enough. I've purchased several resource books for SQL but just don't have the time to spend on this as much as needed at this point.

Any help or suggestions (pointers to web sites etc), is "hugely" appreciated.

I can give more information if needed, but I hope this is basically enough to give you all an idea as to where we are at.

If anyone can suggest a "good" source for DB admins, we would appreciate that as well). Monster, Workopolis etc) have far too many submissions to review at this time.

Thanks in advance Rodger.