Jump to content
  • 0

Run task after data entry?


skcombs
 Share

Question

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.

image.png.e5aec502dbdc7bb7e45a0c23d718f6df.png

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

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?  

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...