Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Identity Column pulling from trigger?

  1. New
  2. Issue
0
Votes
Undo
  1. John Raghanti
    John Raghanti
  2. PowerBuilder
  3. Tuesday, 22 October 2024 18:14 PM UTC

Hi! I'm running PB 2022 R3 Build 3391 with MS SQL Server databases. We just moved from R2 to R3 recently.

I've got a datawindow with an Identity Column specified in the update properties:

I call dw.Update() in code and then (since 2017 at least) I grab transaction_id for the inserted row to set the foreign key for other relevant tables.

I started getting FK errors that it didn't exist. When I step through the code, I am getting a value in the identity column, but it is not the value of the inserted row. There is a trigger on this table that inserts some auditing data (and has since at least 2017), and I am getting the identity value for the auditing entry.

If I insert a row in SSMS, and do a select scope_identity(), it does come back with the correct data for transaction_id.

Did something change in R3? Looking for assistance.

Thanks!

 

Problem with excel clipboard after migrating to 20...
Themes JSON
Who is viewing this page
Roy Bungert
Responses (2)
Jeff Gibson
Jeff Gibson Accepted Answer Pending Moderation
  1. Wednesday, 23 October 2024 21:40 PM UTC
  2. PowerBuilder
  3. # 1

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.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 22 October 2024 20:11 PM UTC
  2. PowerBuilder
  3. # 2

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

Comment
John Raghanti
  1. John Raghanti
  2. Wednesday, 23 October 2024 11:39 AM UTC
I did try to call ReSelectRow() and had the same results. If I Reset() the datawindow and Retrieve() it again, I do get the proper transaction_id. It's very strange. I'm trying to spin up a PB 2022 R2 virtual machine to see what that shows me.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 23 October 2024 20:01 PM UTC
That is very weird John - I would not have anticipated that result.

I wonder if another Trigger is also interfering on the ReSelectRow?

At least you now have a workaround with the Reset / Retrieve though.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.