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.
>
> 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
>
> Thanks to you all, as usual !
>
> -sergio
>

I don't know if this is you real case, but assuming it is.. let's take
another approach... I will talk at very high level, not entering all the
details and implementation...

Before user puts an Invoice, user puts a Sales Order, Sales Order did
not update stock, just indicate that someone wishes some product. On the
first step, the user will insert the Master Record (Sales Order)
indicating Customer ID, payment condition and so on. After that, the
user will start to insert the detail of the sales order (items to be
purchased), and will inform, I want 10 of this, 20 of that, bla bla bla.
He can change it as he wishes, no matter, if change a product, or the
quantity, or the payment method. All updates, deletes and inserts would
occur here only when needed, no need to delete all and insert it all
again, just do what the users did (update the product ID of Item #1,
Delete Item #3, update the Invoice's payment condition, etc.) each of
those changes (updates, deletes or inserts would occur in a distinct
transaction, that starts, make the modifications, commits).

After the user is happy with the quantities, products, payment condition
and so on, he will transform that Sales Order into an Invoice, the
process that transforms it would be in a single transaction and will
update the stock quantities, generate the bills to the costumer based on
the invoice's total value and payment conditions, etc.

In a real life scenario, changing the product or the quantity on the
sales order could performs actions on the database too (summarize the
amount already sold for example), but this is not casted on stone
values, you just need to recalculate it in after insert/delete for the
product in after update triggers you need to update both new.ProductID
and old.ProductID if the ProdutctID changes or new.ProductID if the
quantity changes. Of course you could have a more complex business
rules, but my goal is just to give some direction. Let the user play in
a "sandbox", he can do all he wishes, and just after he is happy with
all the values, process it in a single transaction.

I think it's much simpler than your approach.

Hope I make myself clear :)

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br