SQL DB Insert Trigger Breaks Dexterity Active Locking

Article
06/11/2010

An issue came up recently that I thought I'd share with our readership.

The customer is using SQL insert database triggers on various Dynamics GP tables and running into an issue.

I don't have the full details on the "how, why, and what" but I do know the result of the issue they ran across as detailed below.

Starting with the DB trigger:

SQL DB Trigger on POP10300

create trigger tstACTIVITY on POP10300
for insert
as

insert DYNAMICS..SY05000
select 0 ,'Trigger'
, isnull (A.CMPNYNAM, ''), isnull ( A.USERID, SUSER_NAME() )
, 99
, convert(datetime,convert(char(10),getdate(),112),112)
, convert(datetime,convert(char(12),getdate(),114),114)
, 'Passive lock on POP10300 for ' + i.POPRCTNM
from inserted i,
-- select * from
DYNAMICS..ACTIVITY A join tempdb..DEX_SESSION S
on A.SQLSESID = S.session_id
join master..sysprocesses P
on P.spid = S.sqlsvr_spid
where P.spid = @@SPID

The SQL DB Trigger above will write a record into the Dynamics GP SY_Security_Log table (the Activity Tracking table) when a new record is inserted into the POP10300 table (The POP Receipt Header table).

To understand the issue, a little background on how active locking works in Dexterity.

When the developer wishes to lock a record for exclusive use by the user, they issue a statement like:

change table POP_Receipt, lock;

Or in the case of most transaction entry windows, the code used typically is:

edit table POP_Receipt, lock;

The difference is that an edit table statement will actually write the record in the table buffer to the database at that time and an ordinary change table statement will not do that.

Once an active lock is established on a table, Dexterity writes the DEX_ROW_ID of the newly inserted record or the locked existing record. If another user tries to read that locked record, Dexterity checks the DEX_LOCK table and that is how it knows that another user has the record 'locked'.

Interesting history and architectural lesson but so what?

When Dexterity writes a record into a table, it typically uses the "SI" zDP proc to do the actual insert. For further information on the Dexterity auto-procs, review this blog post.

If you look at the zDP_POP10300SI stored procedure, the last thing it does is return to Dexterity the identity of the inserted row.

SELECT @DEX_ROW_ID = @@IDENTITY

How does that affect this?

According to SQL Books Online and MSDN on the @@IDENTITY function, the function returns the identity of the last inserted record.

From the documentation: If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers.

Uh, oh. That doesn't sound good for us.

And indeed it is not.

If you care to test this by opening the Purchasing Receipt window and entering a new document, you can see this.

Open the Purchase Receipt Entry window and let the Purchase Receipt Number default in. Tab off the field.

In SSMS, run the query:

select DEX_ROW_ID, POPRCTNM from POP10300 order by DEX_ROW_ID desc

Note the DEX_ROW_ID of the top record as that is the record that was just inserted.

Now we check to see the record that Dexterity locked for us by inserting a record into the DEX_LOCK table:

select * from [tempdb]..DEX_LOCK

Unless you run into a coincidence, you'll find that the "row_id" column in the DEX_LOCK table will NOT match the record in the POP10300 table.

If you care to check, run one last query against the trigger inserted table, SY05000:

select * from DYNAMICS..SY05000

Here you'll note that the DEX_ROW_ID of this table is the one that was put into the DEX_LOCK table. This is incorrect.

Now that we know the details, how big a deal is this?

Well, what this means is that potentially both you and I could pull up the same POP Receipt document at the same time and make changes to it which obviously isn't going to work causing errors and damaged data. In theory anyway.

But honestly, I don't think it is a huge deal. It is an issue certainly but not something to panic over.

In reality, the only time I see this happening is the scenario painted by the partner:

Have the SQL DB Insert trigger installed
Have the first user start creating a purchase receipt and note the number
Have the second user open the purchase receipt window
Hand enter the same purchase receipt entered - you can't use the lookup because the non-saved purchase receipt won't show in the lookup.
Notice that Dynamics will let you use the same number where normally it would not. This is because the DEX_ROW_ID used is incorrect.

In other words, you do have to try to make this happen by not defaulting the next document number and it is only on the initial insert that this would happen. If you pull an existing purchase receipt, Dexterity makes the same active lock on the table but this time uses the zDP_POP10300SSxx procedure which reads the DEX_ROW_ID from the table so we don't see this issue.

I did come up with a workaround for the customer however. And I'll note that the method used falls under "not recommended, unsupported, use at your own risk, etc".

While researching the @@IDENTITY function, the documentation mentions a SCOPE_IDENTITY() function that specifically talks about why @@IDENTITY might not work for you (nested scope) and that this function only will pull the identity value in the current scope. Sounds exactly what we need to solve this.

By hand modifying the zDPPOP10300SI, we can switch the line:

SELECT @DEX_ROW_ID = @@IDENTITY

SELECT @DEX_ROW_ID = SCOPE_IDENTITY()

After doing this, I could no longer reproduce the wrong DEX_ROW_ID issue described above.

That said, there are a few concerns I thought of:

Any kind of upgrade will recreate the default zDP procs wiping out your change
Using SQL Maintenance to drop/create the table or the zDP auto procs will wipe out your change
If the SI proc is missing, then Dexterity will fall back to TSQL code that won't have this change. There isn't anything we could do about that.
And while I can't think anything this might hurt by changing this (and certainly seems to fix the issue presented), I'm by no means a SQL Expert. So this might cause unanticipated issues. I don't see how from what the documentation states but you never know.

Regards,

Patrick Roth
Dynamics GP Developer Support

Note: As I noted previously - hand modifying a zDP table autoproc is completely unsupported, use-at-your-own-risk, no warranty implied, not best-practice, etc etc etc. So only do so if you are comfortable doing so, are in fact using a SQL (not Dexterity) DB Insert trigger as documented above, and think you are running into issues as described above. And while a backup is always a good idea - you can always use the SQL Maintenance window in Dynamics to drop/recreate the table autoprocs to put it back to the original state.

SQL DB Insert Trigger Breaks Dexterity Active Locking

Additional resources