Sergio H. Gonzalez wrote:

> From: ""Adam""
>> updates and deletes create record versions which must be garbage
>> collected at a later time, so only update or delete records that you
>> want to (there was a post a few days back where someone was deleting
>> records only to reinsert them).
>
> That "someone" was me, I'm affraid... Sorry for insisting with this, but I'm
> really concerned about do things the way they should be done. I will explain a
> real case and what are my reasons to do what I'm doing. I'm sure, that my
> English doesn't help to make myself clear, but I will try. Of course I WOULD
> LOVE to hear your opinions (being experimented people in DBs)
>
> The case: a user makes an Invoice to a customer. For this example I will use
> just 3 tables
>
> INVOICE_MASTER
> ID = 1 - Date = 2008/09/5 - Customer_ID = 200 - Payment_ID = 1
>
> (Payment_ID = 1 means 3 payments at 30, 60, 90 days)
>
> INVOICE_DETAIL
> Master_ID = 1 - Article_ID = 10 - Quantity = 1 - Unit_Price = 10
> Master_ID = 1 - Article_ID = 11 - Quantity = 1 - Unit_Price = 20
> Master_ID = 1 - Article_ID = 43 - Quantity = 1 - Unit_Price = 20
>
> INVOICE__PAYMENTS (populated by a trigger)
> Due_Date = 2008/10/5 - Amount = 16.66
> Due_Date = 2008/11/5 - Amount = 16.66
> Due_Date = 2008/12/5 - Amount = 16.66
>
> There are some triggers that would update stock quantyties, customer balance,
> etc...
>
> Now the user realizes he made a mistake. The article_id = 43 would not go in
> the invoice and also the customer wants 5 of the article_ID = 11, not 1
>
> This is only a case. Of course the combination of variations is huge: the
> customer_id may change, the payment terms, the invoice date, etc...
>
> So if I have all the actions in "after delete" and "after insert" triggers, I
> think it's much more clear to delete the complete invoice and save it again with
> all the posible changes the user may have introduced. If I dont do this, I would
> have to write a lot of "after update" triggers to contemplate all the possible
> cases of relevant fields that may have changed.

You can postpone all update to stock, customer balance table etc. etc
and then create separate process to periodically update them. In case
you want to know the balance use the query or stored procedure. It will
reduce the complexity in after insert, after update, after delete
trigger but may suffering performance a little bit cause the balances
need to re-calculate every time you need them.

>
> I also must say that I do all the invoice editing in local temp tables, and when
> the user saves the invoice I construct all the SQL and send it to the server.
> Before that, I worked directly into the database inserting and posting every
> record and if the user exited the invoice without saving I did a rollback. But
> later I came up with this idea of working in temp tables "outside" the database
> and then send all the data in a small and very short transaction. I use this
> approach ONLY for master/detail documents (as invoices, sales orders, etc). For
> "simple" tables, of course, I use the known tools everybody uses (in my case IBX
> and ClientDataSets). So... probably, mine is a very bad idea. And I'd LOVE to
> hear other opinions
>

I tried this method too but more comfortable with CachedUpdates.

HTH,
HJ