Best way to detect which columns was updated when using triggers

Question

Hi There

I have have a user table, that has |Firstname|Surname|DateOfBirth| ... and a few other columns. On that specific table i have a trigger on update that if someone changes any user values it creates an audit.

My problem is, management want's a visual audit displayd int the following way:

DateChanged |Change                        |Details
--------------------------------------------------------------------------------
2010/01/01  |FirstName Changed             |FirstName Changed from "John" to "Joe"  
2010/01/01  |FirstName And Lastname Changed|FirstName Changed from "Smith" to "White "And Lastname changed from "els" to "brown"
2010/01/01  |User Deleted                  |"ark Anrdrews" was deleted

I beleive the best way to tackle this is by modifying the stored proc, but how can i instruct the trigger to record these kind of things?

Answer 1

MS SQL Server has a function UPDATED(). It allows you to specify a field name to tell if any records have had that field change.

My experiece, however, is that the functionallity desired often exceed the limits of such a generic function. I normally end up simply joinging the INSERTED pseudo table to the DELETED pseudo table and comparing them myself.

*Such a comparison assumed the table has unique identified, be it a single field or a composite key of somekind.

Answer 2

There's a function you can call within a trigger called COLUMS_UPDATED(). This returns a bitmap of the columns in the trigger table, with a 1 indicating the corresponding column in the table was modified. On it's own that's not brilliant, as you'd end up with a list of column indices, and would still need to write a CASE statement to determing which columns were modified. If you've not got many columns that might not be so bad, but if you wanted something more generic you could perhaps join to information_schema.columns to translate those columns indices into column names

Here's a little sample to show the sort of thing you could do:

CREATE TABLE test (
ID Int NOT null IDENTITY(1,1),
NAME VARCHAR(50),
Age INT

)

GO

CREATE TRIGGER tr_test ON test FOR UPDATE

AS

DECLARE @cols varbinary= COLUMNS_UPDATED()
DECLARE @altered TABLE(colid int)
DECLARE @index int=1

WHILE @cols>0 BEGIN
    IF @cols & 1 <> 0 INSERT @altered(colid) VALUES(@index)
    SET @index=@index+1
    SET @cols=@cols/2
END 

DECLARE @text varchar(MAX)=''
SELECT @text=@text+C.COLUMN_NAME+', '
FROM information_schema.COLUMNS C JOIN @altered A ON c.ordinal_position=A.colid
WHERE table_name='test'

PRINT @text

GO

INSERT test(NAME, age) VALUES('test',12)

UPDATE test SET age=15

UPDATE test SET NAME='fred'

UPDATE test SET age=18,NAME='bert'

DROP TABLE test