Hi John,

If this is your bug that you submitted (https://www.appeon.com/standardsupport/bugfixes/view?id=1232) maybe you've provided more info in it but since it's locked I can't tell.

When you say "...I grab transaction_id...", how are you "grabbing it"? As well, are you able to provide the definition of your trigger (hiding or substituting any confidential information)?

Have you changed database drivers since moving from R2 to R3? Looking at your follow-up post after you spun up a clean machine, the config file provided means you're using an ADO.Net connection as I'm pretty sure non-ADO.Net connections can't use a .NET configuration file. Is this how you're connecting to the database, both in R2 and R3? If you're still using an ADO.Net connection approach, maybe this was an undocumented change for R3. I could have missed it but the information at this link: https://docs.appeon.com/pb2022/connecting_to_your_database/XREF_67762_Defining_the_ADO.html doesn't seem to be available anywhere in the PB R3 Help.

Have you tried doing the insert without the trigger just to see if PB will obtain the correct identity value back without a Reset()/ Retrieve() and rule out anything else going on? Any chance the wrong identity value you are getting, for the initial insert, would match the identity value of any of the newly inserted record(s) for the auditing data succeeded?

@Jeff: "...I think this is normal behavior for the identity attribute..."; I can appreciate the solution you came up with because as far as using @@identity, scope_identity or ident_current(table_name), I tend to stay away from the first two due to the potential for these issues when using triggers and use a different approach.

Maybe you're aware of this...there are two virtual tables that are made available when an insert, update or delete cause a trigger to fire. They are called the INSERTED and DELETED tables (I think it was OLD and NEW in some other DBMSs). If you're not familiar with these tables and how to take advantage of them, you may wish to read up on it as this use case is one of the main advantages with these tables. Using this approach, the FK value will not be dependent on either of @@identity, scope_indentity or ident_current(table_name), but what is written to the INSERTED table which is made available to the trigger, including the identity value.

As to your specific issue, not getting the correct identity value on your initial insert, maybe for some reason you're now coming up against a timing issue that wasn't surfacing before, causing the wrong scope_identity value to be obtained (maybe try and change it to ident_current(table_name) but if it is a timing issue it may still fail, but I'd really look into using the virtual tables mentioned above).  Not sure how much this helps and I might have other suggestions but it would be good to understand some of the questions posed above.

Regards,

Mark

I've got a little more information. We spun up a clean machine and installed PB 2022 R2 build 1892. We have the following section in the PB220.exe.config file, which causes our product to work like it's worked for years.

<configuration>

  <dbConfiguration>

     <myconfig getIdentity="select scope_identity()" />

  </dbConfiguration>

</configuration>

In PB 2022 R3 build 3391 it no longer works. Was this section reformatted or made obsolete?

If I remove this section from R2, I'm seeing the same behavior that I see in R3.

Any thoughts?

I'm uncertain if this will help.

We did something similar to this in a piece of software we had written for the transportation industry.

We would trigger an insert, and then that insert would fire a trigger or triggers that would insert additional rows into detail tables. This would always cause issues with the identity.

We ended up defining connection level variables that associated with each table which had the sole job of just grabbing the identity value.

I think if your identity was working on a table with triggers, you may have just gotten lucky at that moment that you were getting the value before the triggered inserts wrote over the identity value. Someone keep me honest on this. I think this is normal behavior for the identity attribute if a triggered insert is occurring will always get overridden in a situation like this.

We would create our connection level variables on the connect for that user to the database. 

We would call a function to immediately call a remote procedure to grab the identity value.

Not sure if this helps, but this felt very similar to what we had ran into back in the day.

Hi John;

  Since the Trigger seems to be the culprit here. Have you tried using the ReSelectRow() command after the update() command to refresh the DWO's primary buffer with the actual value of the transaction_id column on that parent table?

Regards .. Chris