跟踪更改的字段而不保留历史记录 [英] Tracking changed fields without maintaining history
问题描述
我有一个名为Books的表，其中包含一些列。
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）创建图表为：
ColumnNames: BookId, BookName, BookName_Changed_Flag, BookDesc, BookDesc_Changed_Flag,
xxx, xxx_Changed_Flag?
2）创建一个单独的表Books_Change_Log，就像Books表一样，只有跟踪更改列为： p>
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?
请指教。
- 更新 -
每张表中有20多列。每列表示UI中的某个元素。如果从原始记录中更改列值，我需要显示不同样式的列值的UI元素。
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.
推荐答案
如何在TSQL中使用位域（更新和读取）
How to use a bitfield in TSQL (for updates and reads)
在开始时将位域设置为0（意味着没有更改），您应该使用最多32位数据的类型int和最多64位数据的bigint。
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.
要在位域中设置一点，请使用update语句中的 | （位OR运算符），例如
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
等于每个字段使用2到N-1的电源为 |
etc for each field use the 2 to power of N-1 for the value after the |
要读取一个位字段，请使用& AND运算符），并查看是否为true，例如
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
注意我可能不会使用文本，因为这将被应用程序使用， p>
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
或者你可以使用上面的！= 0来简化我在下面测试中的简单
or you can use != 0 above to make it simple as I did in my test below
原始答案：
如果您有您的表中的列少于16列，您可以将标志存储为整数，然后使用位标志方法来指示更改的列。只要忽略或不要打扰你不关心的那些。
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.
因此，如果flagfield BOOLEAN AND 2 ^ N为真，则表示第N个字段已更改
Thus if flagfield BOOLEAN AND 2^N is true it indicates that the Nth field changed.
或最大N = 2的示例
Or an example for max of N = 2
0 - 没有变化（全部为0）
0 - nothing has changed (all bits 0)
1 - 字段1已更改（第1位）
1 - field 1 changed (first bit 1)
2 - 字段2已更改（第二位1）
2 - field 2 changed (second bit 1)
3 - 字段1 + 2更改（第一个和第二个位1）
3 - field 1+2 changed (first and second bit 1)
看到这个链接更好定义： http://en.wikipedia.org/wiki/Bit_field
see this link for a better definition: http://en.wikipedia.org/wiki/Bit_field
这篇关于跟踪更改的字段而不保留历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
