Track changed fields without saving history
I have a table named Books that contains multiple columns.
ColumnNames: BookId, BookName, BookDesc, xxx
I want to track changes for specific columns. I don't need to maintain the history of the old meaning and the new meaning. I just want to keep track of whether this value has changed or not.
What is the best way to achieve this?
1) Create a table of books as:
ColumnNames: BookId, BookName, BookName_Changed_Flag, BookDesc, BookDesc_Changed_Flag,
xxx, xxx_Changed_Flag?
2) Create a separate Books_Change_Log table just like the Books table, but only with track change columns like:
ColumnNames: BookId, BookName_Changed_Flag, BookDesc_Changed_Flag, xxx_Changed_Flag?
Please advise.
- Update -
Each table contains more than 20 columns. And each column represents a specific element in the user interface. If the column value is ever changed from its original entry, I need to display a UI element that represents the column value in a different style. The rest of the elements should look fine.
source to share
How to use bits in TSQL (for updates and reads)
Set the bit in by default to 0 at startup (which means no change), you should use 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 an operator |
(bit OR operator) in an 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 value 2 for N-1 for the value after|
To read a bitfield use &
(AND bit 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 as this will be used by the 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 like it was in my test below
Be sure to test to have no errors, click for test script
original answer:
If your table has less than 16 columns, you can store the "flags" as an integer and then use the bit flag method to indicate the columns that changed. Just ignore or don't get in the way of labeling the ones you don't need.
Thus, if the BOOLEAN AND 2 ^ N flag is true, this indicates a change in the Nth field.
Or an example for max N = 2
0 - nothing has changed (all bits are 0)
1 - field 1 changed (first bit 1)
2 - changed field 2 (second bit 1)
3 - changed field 1 + 2 (first and second bit 1)
see this link for a better definition: http://en.wikipedia.org/wiki/Bit_field
source to share
You should have a log table that stores the BookId and modified date (you don't need these other columns), as you said, you don't need the old and new values, and you can always get the current value of name, description, etc. from the "Books" table, there is no reason to store it twice). If you are not only interested in the last time, it has changed. You can populate the journal table with a simple refresh trigger on the books table. For example, with the new information you 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
source to share
I know you said you didn't need this, but sometimes just use something from the shelf that does everything, like this: http://autoaudit.codeplex.com/
This just adds a few columns to your table and is not as invasive as any of your suggested schemas, and the trigger needed to track changes is also generated by the tool.
source to share
I can guarantee that upon delivery of this solution, one of the following requests will be "show me what happened before". Just go ahead and get the history table. This will solve your current problem and your future problem. This is a fairly standard design for non-trivial systems.
source to share
Place two datetime columns in your table, "created_at" and "updated_at". By default as current_timestamp. Only ever set the update_at value if you are changing data in a row. You can enforce this by using a trigger on the table that checks to see if any value in the column changes and then updates "updated_at" if so.
If you want to check if a string has changed, just check if update_at> created_at has been updated.
source to share