Tracking changed fields without maintaining histor

问题:

I have a table named Books which contains some columns.

ColumnNames: BookId, BookName, BookDesc, xxx

I want to track changes for certain columns. I don't have to maintain history of old value and new value. I just want to track that value is changed or not.

What is the best way to achieve this?

1) Create Books table as:

ColumnNames: BookId, BookName, BookName_Changed_Flag, BookDesc, BookDesc_Changed_Flag, 
xxx, xxx_Changed_Flag?

2) Create a separate table Books_Change_Log exactly like Books table but only with track change columns as:

ColumnNames: BookId, BookName_Changed_Flag, BookDesc_Changed_Flag, xxx_Changed_Flag?

Please advise.

--Update--

There are more than 20 columns in each table. And each column represents a certain element in UI. If a column value is ever changed from its original record, i need to display the UI element that represents the column value in different style. Rest of the elements should appear normal.

回答1:

How to use a bitfield in TSQL (for updates and reads)

Set the bitfield to default to 0 at start (meaning no changes) you should use type int for up to 32 bits of data and bigint for up to 64 bits of data.

To set a bit in a bit field use the | (bit OR operator) in the update statement, for example

UPDATE table 
SET field1 = 'new value', bitfield = bitfield | 1

UPDATE table 
SET field2 = 'new value', bitfield = bitfield | 2

etc for each field use the 2 to power of N-1 for the value after the |

To read a bit field use & (bit AND operator) and see if it is true, for example

SELECT field1, field2,
       CASE WHEN (bitfield & 1) = 1 THEN 'field1 mod' ELSE 'field1 same' END,
       CASE WHEN (bitfield & 2) = 2 THEN 'field2 mod' ELSE 'field2 same' END
FROM table

note I would probably not use text since this will be used by an application, something like this will work

SELECT field1, field2,
        CASE WHEN (bitfield & 1) = 1 THEN 1 ELSE 0 END AS [field1flag],
        CASE WHEN (bitfield & 2) = 2 THEN 1 ELSE 0 END AS [field2flag]
FROM table

or you can use != 0 above to make it simple as I did in my test below

---

Have to actually test to not have errors, click for the test script

---

original answer:

If you have less than 16 columns in your table you could store the "flags" as an integer then use the bit flag method to indicate the columns that changed. Just ignore or don't bother marking the ones that you don't care about.

Thus if flagfield BOOLEAN AND 2^N is true it indicates that the Nth field changed.

Or an example for max of N = 2

0 - nothing has changed (all bits 0)

1 - field 1 changed (first bit 1)

2 - field 2 changed (second bit 1)

3 - field 1+2 changed (first and second bit 1)

see this link for a better definition: http://en.wikipedia.org/wiki/Bit_field

回答2:

I know you said you don't need it, but sometimes it's just easier to use something off the shelf which does everything, like: http://autoaudit.codeplex.com/

This just adds a few columns to your table and is not nearly as invasive as either of your proposed schemas, and the trigger necessary to track the changes are also generated by the tool.

回答3:

You should have a log table that stores the BookId and the date of the change (you don't need those other columns - as you stated, you don't need the old and new values, and you can always get the current value for name, description etc. from the Books table, no reason to store it twice). Unless you are only interested in the last time it changed. You can populate the log table with a simple for update trigger on the books table. For example with the new information you've provided:

CREATE TABLE dbo.BookLog
(
  BookID INT PRIMARY KEY,
  NameHasChanged BIT NOT NULL DEFAULT 0,
  DescriptionHasChanged BIT NOT NULL DEFAULT 0
  --, ... 18 more columns
);

CREATE TRIGGER dbo.CreateBook
ON dbo.Books FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.BookLog(BookID) SELECT BookID FROM inserted;
END
GO

CREATE TRIGGER dbo.ModifyBook
ON dbo.Books FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t SET 
        t.NameChanged = CASE WHEN i.name <> d.name 
          THEN 1 ELSE t.NameChanged END,
        t.DescriptionChanged = CASE WHEN i.description <> d.description 
          THEN 1 ELSE t.DescriptionChanged END,
           --, 18 more of these assuming all can be compared with simple <> ...
    FROM dbo.BookLog AS t
    INNER JOIN inserted AS i ON i.BookID = t.BookID
    INNER JOIN deleted AS d  ON d.BookID = i.BookID;
END
GO

回答4:

I can guarantee you that after you deliver this solution, one of the next requests is going to be "show me what it was before". Just go ahead and have a history table. That will solve your current problem AND your future problem. It is a pretty standard design on non-trivial systems.

回答5:

Put two datetime columns in your table, "created_at" and "updated_at". Default both to current_timestamp. Only ever set the value of updated_at if you are changing the data in the row. You can enforce this with a trigger on the table that checks to see if any of the column values are changing, and then updates "updated_at" if so.

When you want to check if a row has ever changed, just check if updated_at > created_at.