This is a reposting of an article Patrick Roth originally wrote on my Developing for Dynamics GP blog.
An ISV had a goal of creating an audit table of the Dynamics record notes table – SY03900 – but was running into an issue.
He mentioned that he was using an “instead of update” SQL trigger and not just a normal “update” trigger. The specific issue was that while his audit table was being updated, the SY03900 table was not.
After discussing the goals of his application and the issue, I set out to discover why exactly the normal update trigger wouldn’t work, why the SY03900 table wasn’t being updated, and lastly what we could do about it.
To start; while I’m not a novice in SQL I’m hardly an expert either. I was puzzled on why a normal update trigger wouldn’t work. I’ve used them before for various reasons and I know support very often makes use of SQL DB update triggers to shadow GP tables for support cases.
So the thing to look at is – why not just use a regular update trigger?
I created an audit trail table, entered the text below in SSMS to create the trigger, and executed it to create the trigger.
SQL DB Update Trigger (first try)
create trigger SY03900_U on SY03900 FOR UPDATE as begin insert into SY03900_AUDIT (NOTEINDX,DATE1,TIME1,TXTFIELD_NEW) select b.NOTEINDX,b.DATE1,b.TIME1,b.TXTFIELD from inserted b end
Oh, that’s what the ISV meant. You really can’t do this – it fails to run with the error:
Msg 311, Level 16, State 1, Procedure SY03900_U, Line 6
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
Mystery one down, can’t do it that way so we have to use the “instead of update” syntax like the ISV did.
The 2nd mystery is easy also – the ISV says he is using a an “instead of update” trigger which sounds to me (and confirmed in SQL Books Online) that it truly runs “instead of” the actual update statement. So in this case the SY03900 table isn’t being updated because the SQL DB trigger isn’t letting it update and the ISV isn’t updating the SY03900 table in the trigger itself.
So lets use the “instead of update” syntax like the ISV was doing:
SQL DB Update Trigger (2nd try)
create trigger SY03900_U on SY03900 INSTEAD OF UPDATE as begin insert into SY03900_AUDIT (NOTEINDX,DATE1,TIME1,TXTFIELD_NEW) select b.NOTEINDX,b.DATE1,b.TIME1,b.TXTFIELD from inserted b end /*now test it to make sure it works*/ update SY03900 set TXTFIELD = 'cats' where NOTEINDX = 179
This does execute and create the SQL trigger on the table as expected. And when examining the SY03900_AUDIT table, the expected record was inserted. But as the ISV found, when looking at the SY03900 table with NOTEINDX= 179, the TXTFIELD value is not ‘cats’ and is still ‘dogs’.
So my audit table didn’t really audit correctly and prevented the update from happening (which is bad).
But at least we know what we need to do now – use an instead of update db trigger and then also make sure to update the table being audited with the changes.
SQL DB Update Trigger (last try)
drop table SY03900_AUDIT go CREATE TABLE [dbo].[SY03900_AUDIT]( [NOTEINDX] [numeric](19, 5) NOT NULL, [DATE1] [datetime] NOT NULL, [TIME1] [datetime] NOT NULL, [TXTFIELD_OLD] [text] NULL, [TXTFIELD_NEW] [text] NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] go drop trigger SY03900_U go create trigger SY03900_U on SY03900 INSTEAD OF UPDATE as begin insert into SY03900_AUDIT (NOTEINDX,DATE1,TIME1,TXTFIELD_OLD,TXTFIELD_NEW) select b.NOTEINDX,b.DATE1,b.TIME1,a.TXTFIELD,b.TXTFIELD from SY03900 a inner join inserted b on a.NOTEINDX = b.NOTEINDX /* Only insert audit record if TXTFIELD actually changed and not the DATE1 or TIME1 field. Used "like" in where clause because SQL 2008 wouldn't let me use <>. Seems to work fine in that old value of 'abc' is apparently not "like" 'abcd' and so the insert does happen. */ where a.TXTFIELD not like b.TXTFIELD /* lastly update the SY03900 table with the updated data */ update a set a.TXTFIELD = b.TXTFIELD, a.DATE1 = b.DATE1, a.TIME1 = b.TIME1 from SY03900 a inner join inserted b on a.NOTEINDX = b.NOTEINDX end --test the trigger update SY03900 set TXTFIELD = 'cats' where NOTEINDX = 179 update SY03900 set TXTFIELD = 'dogs' where NOTEINDX = 179 update SY03900 set TXTFIELD = 'dogs' where NOTEINDX = 179 update SY03900 set TXTFIELD = 'birds' where NOTEINDX = 179 update SY03900 set TXTFIELD = 'frogs' where NOTEINDX = 179
The above code creates an audit trail of the SY03900 showing both the “old” and “new” values of the TXTFIELD for the record note. If the TXTFIELD wasn’t updated, then we don’t log an audit record.
In the above script, we do 5 update statements however we only get 4 records in the audit table. The reason is that we update TXTFIELD to ‘dogs’ twice in a row and therefore the update isn’t recorded. This was done purposefully as the comment in the TSQL above shows – only update the shadow table if the TXTFIELD data actually is changed.
A screenshot of my audit table is below which shows the capture of the audited data as well as the final result of the TXTFIELD in the SY03900.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.