triggers updating a record behind

[Jodie]

Ok so I've had limited success with triggers to date and so I've avoided them but it's meant my pages do way too much work so I'm trying to get some very simple triggers to work. But in my very first one, somehow I've ended up with a lag in my trigger.

I've created a dummy app with dummy data. It had initially worked, but when I realised another part of the trigger was always replacing all the data, I disabled that version and deleted all the records to start again. And that's when everything started to run behind.

I've attached the trigger.

But in essence this is what happens. I create a new record in my profilebase table (record 6 and 7). I add data through a datapage into the mimic table. I expect the trigger to copy that data into my profilebase table once the record is marked "complete". But when I enter record 6 and mark complete, the profilebase table doesn't update record 6. When I enter record 7 and mark complete, the trigger updates profilebase's record 6. Record 7 remains out of sync.

Every trigger example I watch or read shows the impact instantaneous - how have I managed to mess this up??? 

[Jodie]

Ok so it appears that when I made changes to my trigger I didn't notice my join statement changed and I think this with the deleted records messed this one up. I started from scratch and joined on the inserted# table and now no matter how many records I delete this trigger works fine!

 

[NiceDuck]

Yep, that is exactly the miss on this one.

If you are working with triggers, it's very likely that the virtual table #inserted (for insert and update) and #delete are involved. Those virtual tables serve as a placeholder for the values that just got inserted, updated, or deleted depending on the workflow. 

If you simply refer to the parent table name instead of the virtual table, you are actually referring to the values of the table before the specific action has occurred, that is why you noticed that your trigger is somewhat behind the process.