Identify Row as having changes excluding changes in certain columns

Within our business rules, we need to track when a row is designated as being changed. The table contains multiple columns designated as non-relevant per our business purposes (such as a date entered field, timestamp, reviewed bit field, or received bit field). The table has many columns and I'm trying to find an elegant way to determine if any of the relevant fields have changed and then record an entry in an auditing table (entering the PK value of the开发者_运维知识库 row - the PK cannot be edited). I don't even need to know which column actually changed (although it would be nice down the road).

I am able to accomplish it through a stored procedure, but it is an ugly SP using the following syntax for an update (OR statements shortened considerably for post):

INSERT INTO [TblSourceDataChange] (pkValue)
    SELECT d.pkValue
    FROM deleted d INNER JOIN inserted i ON d.pkValue=i.pkValue
    WHERE (    i.[F440] <> d.[F440]
          OR i.[F445] <> d.[F445]
          OR i.[F450] <> d.[F450])

I'm trying to find a generic way where I could designated the ignore fields and the stored proc would still work even if I added additional relevant fields into the table. The non-relevant fields do not change very often whereas the relevant fields tend to be a little more dynamic.

Have a look at Change Data Capture. This is a new feature in SQL Server 2008.

First You enable CDC on the database:

EXEC sys.sp_cdc_enable_db

Then you can enable it on specific tables, and specify which columns to track:

EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'xxx',
    @supports_net_changes = 1,
    @role_name = NULL, 
    @captured_column_list = N'xxx1,xxx2,xxx3'

This creates a change table named cdc.dbo_xxx. Any changes made to records in the table are recorded in that table.

I object! The one word I cannot use to describe the option available is elegant. I have yet to find a satisfying way to accomplish what you want. There are options, but all of them feel a bit unsatisfactory. When/why you chose these options depends on some factors you didn't mention.

• How often do you need to "ask" what fields changed? meaning, do users infrequently click on the "audit history" link? Or is this all the time to sort out how your app should behave?
• How much does disk space cost you ? I'm not being flippant, but i've worked places where the storage strategy for our auditing was million dollar issue based on what we were being charged for san space -- meaning expensive for SQL server to reconstitute wasn't a consideration, storage size was. You maybe be the same or inverse.

Change Data Capture

As @TGnat mentioned you can use CDC. This method is great because you simply enable change tracking, then call the sproc to start tracking. CDC is nice because it's pretty efficient storage and horsepower wise. You also kind of set it and forget it---that is, until developers come along and want to change the shape of your tables. For developer sanity you'll want to generate a script that disables/enables tracking for your entities.

I noticed you want to exclude certain columns, rather than include them. You could accomplish this with a FOR XML PATH trick. You could write a query something like the following, then use the @capturedColList variable when calling sys.sp_cdc_enable_table ..

 SET @capturedColList =   SELECT Substring( (
                SELECT ',' + COLUMN_Name
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = '<YOUR_TABLE>' AND
                      COLUMN_NAME NOT IN ('excludedA', 'excludedB')

                FOR XML PATH( '' )  
            )  , 2, 8000)

Triggers w/Cases The second option I see is to have some sort of code generation. It could be an external harness or a SPROC that writes your triggers. Whatever your poison, it will need to be automated and generic. But you'll basically code that writes DDL for triggers that compare current to INSERTED or DELETED using tons of unweildy CASE statements for each column.

There is a discussion of the style here.

Log Everything, Sort it out later

The last option is to use a trigger to log every row change. Then, you write code (SPROCS/UDFs) that can look through your log data and recognize when a change has occured. Why would you choose this option? Disk space isn't a concern, and while you need to be able to understand what changed, you only rarely ask the system this question.

HTH,

-eric

Use a trigger and make sure it can handle multiple row inserts.

I found the answer in the post SQL Server Update, Get only modified fields and adapted the SQL to fit my needs (this sql is in a trigger). The SQL is posted below:

DECLARE @idTable INT SELECT @idTable = T.id FROM sysobjects P JOIN sysobjects T ON P.parent_obj = T.id WHERE P.id = @@procid

IF EXISTS (SELECT * FROM syscolumns WHERE id = @idTable
AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0 AND name NOT IN ('timestamp','Reviewed') ) BEGIN --Do appropriate stuff here END