0

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get back the actual field name. I want to get the value, so I don't have to explictly name each field (like I did when I set the NewValue - this works perfectly). Trying to avoid this because, just like most databases, we have tables with several fields. I'm stuck with the syntax on this. Would like to do something like:

@OldValue = (Select 'd.' + @fieldname from deleted)

Here's my code:

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT  @TableName = N'TESTAUDITTABLE'
SELECT  @PKCols =  column_name
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                    INNER JOIN
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                    TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                    and ku.table_name= @TableName
                    ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

SELECT  @UserName = SYSTEM_USER
SELECT  @UpdateDate = {fn now()}

SELECT  @field = 0 
SELECT  @maxfield = MAX(ORDINAL_POSITION) 
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

WHILE   @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field

                SELECT @ID = coalesce( i.ORIGREC , d.ORIGREC ) ,
                         @Type = case
                                       when i.ORIGREC is not null and d.ORIGREC is     null then 'Insert' -- insert
                                       when i.ORIGREC is not null and d.ORIGREC is not null then 'Update' -- update
                                       when i.ORIGREC is     null and d.ORIGREC is not null then 'Delete' -- delete
                                     end,
                 @OldValue = (Select @fieldname from deleted), 
                 @NewValue = (Select i.NAME from #ins)

                FROM inserted i
                FULL join deleted d on d.ORIGREC = i.ORIGREC

               SELECT @sql = '
                    INSERT INTO [dbo].[LE_AUDIT] (
                        DB_USER,
                        TableName,
                        ID,
                        FieldName,
                        OldValue,
                        NewValue,
                        CHANGEDATE,
                        TRIG_TYPE)                      
                    VALUES (''' + SYSTEM_USER + ''',''' + @TableName +
                    ''',''' + @ID + ''',''' + @fieldname + ''',''' + 
                    @OldValue + ''',''' + @NewValue + ''',''' +  @UpdateDate  + ''',''' + @Type + ''')' 
               EXEC (@sql)

               print @sql
       END
END

The trigger works fine. Here is the original source for the link. https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

I had to modify it to force it to work in conjunction with another app. Just need to modify the one line to capture the value without specifying the actual field name in the while loop.

  • Noted you are using ORDINAL_POSITION. Per BOL : The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example. – Serg May 5 '16 at 21:01
  • Your trigger seems to assume that either (a) it will execute independently for every row or (b) only one row will ever be affected. – Aaron Bertrand May 5 '16 at 22:44
  • For inserts and deletes, why not just store a copy of the row that was either added or removed? You're going to store a row for every column in both cases, so I don't see why all the rigamarole of auditing a column at a time. In fact for the update I would probably store the before and after copy of the row. Also, note that for updates, COLUMNS_UPDATED() only tells you that a column was referenced in the update statement - it doesn't mean that the value has actually changed. Finally, there are already solutions for this - Change Tracking, CDC, Temporal. Have you considered any of those? – Aaron Bertrand May 5 '16 at 22:55
0

I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes?

Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye opening and a really cool and simple way of tracking changes such as UPDATES or DELETES.

Assume we have an Orders table with 4 columns: orderid, custid, empid, and orderdate. If we wanted to create an audit table that tracks delete statements the code would be:

DELETE FROM dbo.Orders
OUTPUT
    deleted.orderid,
    deleted.orderdate,
    deleted.empid,
    deleted.custid
Into dbo.OrdersAudit
WHERE orderid = '5';

This code will run your delete statement, and store the all the values into your audit table. The OUTPUT clause can also be used to track your inserts and updates. From what I can tell, the downside is you have to incorporate the OUTPUT clause into all of your procedures. But I wasn't even aware of this clause, and it's super simple to learn the syntax. This probably wasn't what you were looking for, but I was really excited when I read about this clause, and looks like an easy way to track data changes for audits.

  • Thanks Luke. This sounds cool. But, yes I cannot modify the SP's we have. I have to do it this way, even though I know it's not the a good idea. – bflow1 May 5 '16 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.