James wrote:

> My tables are payment and payment_details(they're have master detail
> relationship ). In the payment table stores the information like who
> made the payment and how much, and on the payment_details stores the
> information on what invoices it pays and how much.
>
> I have a trigger on after insert of the payment_details which is use to
> update the apply_amount on the payment table(to track the usage of the
> payment) and the paid_amount on the invoice(to track the amount paid to
> the invoice) table. And Iam choosing between the two script.
>
> First:
>
> update payment
> set apply_amount = (select sum(amount)
> from payment_details
> where master_key = new.master_key and status != 'CAN')
> where id = new.master_key;
>
>
> update invoice
> set paid_amount = (select sum(amount)
> from payment_details
> where invoice = new.invoice and status != 'CAN')
> where id = new.invoice;
>
> Second:
>
> update payment
> set apply_amount = apply_amount + new.amount
> where id = new.master_key;
>
> /* updates the invoice table */
> update invoice
> set paid_amount = paid_amount + new.amount
> where id = new.invoice;
>
> My concern is on speed and reliability. If the first script is to be use
> I can use this script not only on after insert trigger but also in after
> update and after delete trigger. But to use the second script I have to
> slightly edit some like in after delete instead of "set apply_amount =
> apply_amount + new.amount" it could be "set apply_amount = apply_amount
> - new.amount" instead. and on after update trigger I might have to write
> the update statement twice, one is for subtracting the old value like
> this "set apply_amount = apply_amount - old.amount" and second could be
> "set apply_amount = apply_amount + new.amount"
>
> Please advice on the following issue. Hope to learn from you GURUS :-)

Do you 'need' apply_amount or paid_amount ?
That may sound a silly question, but the answer points out where to go.

If you have a list a 'payments' as in the case of the bank account, then
the total paid will be logged before you 'allocate' it to individual
items. So you 'reconcile' the total payment against your
payment_details. In theory they always match up, but sometimes too much
or too little will have been paid, and you end up with a
'payment-on-account' or an 'underpay', so "paid_amount" does not match
"amount_paid" :)

How you handle that depends on your system, but rather than a trigger
generating a number, you may be better off with just using the

(select sum(amount)
from payment_details
where master_key = new.master_key and status != 'CAN')

In a later query when you need the value.

I'm probably out of line here, but just trying to point out that
calculating totals line this may not be the best solution, using the
'relational data' when required may be more appropriate.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services