bug (?): truncation in update trigger

Sep 30, 2015 at 4:15 PM

There is code that lets the user of AutoAudit change the length of the NewValue and OldValue columns in the audit table. In fact there are comments that say "edit to suit" and suggest that you can change it to things like VARCHAR(MAX) etc. However there appear to be two bugs:
  • You cannot actually change it to VARCHAR(MAX) or NVARCHAR(MAX). When I tried, I got an error that said (in effect) that type was not valid to be used as a key.
  • I did succeed in changing it to NVARCHAR(4000) which is not ideal for me, but it's better than nothing; however the update trigger actually truncates the audited values to VARCHAR(50). This appears to be because of this line:
Declare @AuditDetailUpdate Table (PrimaryKey VARCHAR(250), ColumnName sysname, OldValue varchar(50), NewValue varchar(50))

I'm going to try to change THAT to NVARCHAR(4000) and see how that goes... any help appreciated as I'm trying to propose this as an auditing solution for my client. It would be nice to fix both issues so it's permissible to use NVARCHAR(MAX) as the length.

Thank you,
Dave Barrows
Sep 30, 2015 at 4:17 PM
Actually one more thing on this... ideally this would be configurable, via a variable declaration at the top, like some of the other settings.
Sep 30, 2015 at 4:26 PM
Actually the insert trigger also truncates to varchar(50)