Trouble finding correct syntax in triggers to calculate an amount in a loop

[Mary99]

I've been trying to set triggers up to create a payment table for amortized loans.  I'm able to get all the fields to work correctly except updating the principal amount paid each month.  On the insert of a loan table, I'm creating a payment table based on the number of payments, loan loan amount and interest rate passed.  I also pass the initial principal payment although it seems like I should be able to do the necessary calculation in the triggers.   Most of the trigger works as expected buy I can seem to find the correct combination to correctly update the Principal Payment.  I've tried various combinations and keep running into various errors when I try different ways.  Please let me know what I'm doing wrong. 

 

 

 

Edited October 27, 2022 by Mary99
Included the wrong table image

[vidierre]

On 10/27/2022 at 6:51 AM, Mary99 said:

I've been trying to set triggers up to create a payment table for amortized loans.  I'm able to get all the fields to work correctly except updating the principal amount paid each month.  On the insert of a loan table, I'm creating a payment table based on the number of payments, loan loan amount and interest rate passed.  I also pass the initial principal payment although it seems like I should be able to do the necessary calculation in the triggers.   Most of the trigger works as expected buy I can seem to find the correct combination to correctly update the Principal Payment.  I've tried various combinations and keep running into various errors when I try different ways.  Please let me know what I'm doing wrong. 

 

 

 

It is not very clear to me you expetctation.  From trigger you show it seems work as designed.
It would be useful for us, if you detail here some formula or numeric example that show field relationships.

[Mary99]

I was able to get it to work by looping a bit differently.  But now I'm trying to figure out how to adjust the trigger to update the table in the case where the rate, loan term or loan amount is changed.  I'm including an example of the functioning insert trigger and a copy of the payment table that shows the correct insert response.  I'm new to triggers so I'm stumbling on how to now adjust the trigger for updates.  When any of the original amounts (rate, term, loan amt) change, I want to update this table to reflect the schedule based on those new amounts.  Any help is appreciated.

In the table below, you can see where the pmt_principal amount is getting updated correctly (pmt_amount_due - pmt_interest = pmt_principal).