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)
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)
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)
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.