2

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Penniless Tim
  • 21
  • 1
  • 2
  • I've added a new answer to this old question that is relevant: http://stackoverflow.com/questions/1254787/sql-server-update-trigger-get-only-modified-fields/8020461#8020461 – Stephen Turner Nov 05 '11 at 13:36

4 Answers4

1

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.

TGnat
  • 3,903
  • 8
  • 35
  • 46
  • This could be a solution, but I'm really trying to use a methodology where I specific which columns not to track (if possible). – Penniless Tim Feb 23 '11 at 20:28
  • Contemplating the binary-checksum or checksum-agg (but not real familiar with them and if they would work) – Penniless Tim Feb 23 '11 at 20:29
  • You could look at Change Tracking. Which is like Change Data Capture Lite. You don't have to specify any columns to track (I think) but you get less information about the changes (Deletes specifically). See: http://msdn.microsoft.com/en-us/library/cc280462.aspx – TGnat Feb 23 '11 at 20:40
  • CDC will only track changes to the Columns that you are interested in. If a change happens to a column that you are not tracking then you won't be notified of the change. – TGnat Feb 23 '11 at 20:54
1

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

Community
  • 1
  • 1
EBarr
  • 11,826
  • 7
  • 63
  • 85
  • Thanks for the info! What is confusing about my question is that it appears as if I'm auditing changes - I'm not (disk space is not an issue). Our system calculates tax returns and we have tables identified as source data - a change in any of the values will affect a tax calculation. Our system looks into the TblSourceDataChange table (it contains additional fields not listed) and identifies calculations with changed source data. We don't do column level as the calculations are very complex and we just need a 'probably' changed results. We use same table structure each subsequent year. – Penniless Tim Feb 24 '11 at 12:53
  • @user -- while your use case is different from what i mentioned above, it sounds like "log and sort it out later" would be appropriate. Although, it's not clear what the frequency of your changes are (annually?). I didn't mention how to "sort it out later" but you can effectively do some clever group by clauses so your query generates a distinct row per change. You'll want to automate generation of these queries. HTH, -eric – EBarr Feb 24 '11 at 15:53
0

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

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

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

Community
  • 1
  • 1
Penniless Tim
  • 21
  • 1
  • 2
  • FYI : Although the COLUMNS_UPDATED() function seems to indicate that a column has changed, this is not quite always true. It means a column was 'touched', but it might well have been : UPDATE myTable SET myField = MyField. Then again, it's probably a good start on top of the trigger so you don't need to go matching inserted & deleted if none of the columns was touched. PS: ColumnsUpdated is way faster than checking each column apart with UPDATE() ! – deroby May 23 '11 at 12:43