Using SQL DB Update Trigger With Text Fields

Patrick Roth - Click for blog homepage

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.

Best regards,
Patrick Roth