hi

I could like to have your opinion on by trigger script.

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 :-)

regards,
james