Run task after data entry?

[skcombs]

Situation:  Parent LOAN table includes fields: Date, Amount, SumOfPayments, MostRecentPayment, MostRecentPaymentAmount

Child PAYMENTS table includes fields:  PaymentDate, PaymentAmount

When a PAYMENT is inserted,  the SumOfPayments should increase by PaymentAmount, and if Payment is deleted, SumOfPayments should decrease by that amount.  Easy enough in a trigger.  The problem comes in the MostRecent fields.  I can create a TASK to reorder PAYMENT table entries by PaymentDate and then reset those fields in the LOAN table,  and loop thru PaymentAmounts to recalculate SumOfPayments.   But triggers occur mid-transaction, so too early to set the MostRecent fields, or do I misunderstand?   I would like to run the  task immediately after the insert/delete/update takes place.  Is there any way to do this?

Also, in a tabular report that allows inline edit and where the results are ordered by PaymentDate, I do not see a reordering after editing/updating  the Payment Date.  If I reload the results, the order is correct, but not if I edit inline. Here is an example of the results after changing the first row's date to occur before the date in the second row.

[NiceDuck]

Okay, I kinda lost it on why triggers wont work on your case, they are the one that is supposed to react when there's an activity on your table. 

 

[skcombs]

If I am inserting a new payment that has a payment date and a payment amount,  I cannot know whether the payment date is the most recent date of payments for that loan. It could be that someone is entering a date that is prior to other dates in the payments table.

I have triggers invoked when a payment is made or deleted.  When a payment is entered into the Payments table,  I thought I could update the MostRecentPaymentDate in the parent Loans table by saying MostRecentPaymentDate =  SELECT TOP 1 from PaymentsTable. Payment Date, Order By PaymentDates (desc) in the Payments table. I'm not getting the result I would expect if the newly inserted date is included in the sort.  Is this because the insertion/deletion hasn't completed yet?  

[NiceDuck]

I think I have the issue now, do you mean that you want to update the date of the record on your table b (parent Loans table) with the latest date from your table a (Payments table) including the one you just inserted. 

However, what happens is, your select top 1 from payments table does not detect the record you just inserted.

Is that the case?

If yes, that is indeed because the record you just inserted is not yet on the table yet during the trigger operation. They are actually on a virtual table called #inserted. They will only be an actual member of that table right after the trigger operation ends. 

I can provide you a workaround for this, however, I want to know first if I got you correct.

[skcombs]

You have it exactly right. Thanks.

[NiceDuck]

On this case, you will only need to replace the table reference of your triggers. Instead of using the Table name, you will have to use the #inserted Instead. By the way, the trigger must be on the actual table where the activity occurs and not on the table referenced. If you could provide me a screenshot of your trigger, perhaps I can point out exactly what needed to be changed.