In the past I have just added an field to each table and updated it with GETDATE() on every update/insert. The problem is now I have to keep track of delete too. I was thinking of just having a table that I would update when anything changed and add a trigger to all of the other tables. Ideas??? Thanks!
A:
Flag the records as deleted=1 and do not delete it. Do a trigger on delete instead update...
Pentium10
2010-02-01 19:45:34
+1
A:
I've seen tables designed with a bit field as IsDeleted and default value of course is set to false. When an item is deleted this value is set to true. All queries would then need to take this into affect:
SELECT blah FROM myTable WHERE IsDeleted=0
This way if you "accidentally" deleted a row, you should be able to bring it back. You could also purge records on say a weekly / monthly / yearly basis.
That is just an idea for you.
JonH
2010-02-01 19:46:24
Many DBs also use the effective date concept which is somewhat similar.
Sam
2010-02-01 20:08:16
+4
A:
If you have a history table (A table with the same columns as the original table, plus an auto-increment ID column), you can track everything about changes to the original table. You can track inserts, deletes, and every change. Use triggers for insert, update, and deletes to put a row into the history table. If you don't need all these options, then use those that you do need.
If you choose to use an IsDeleted flag in the original table, it complicates every query, and leaves your active table with lots of unneeded rows. But that can work, depending on your needs.
aaaa bbbb
2010-02-01 19:51:15
@aaaa bbbb - In some places that is a requirement to ensure getting data back that might have been lost. That info could be backed up and then purged weekly. An additional field especially of bit type is VERY VERY minimal.
JonH
2010-02-01 19:54:45
@JonH - I agree the bit field can be reasonable. The history table approach allows for better tracking of changes. And the history table approach immediately gets deleted data out of the active table.
aaaa bbbb
2010-02-01 19:59:52
Keep in mind that this will cause overhead - and you may want to disable for massive data loads.
Sam
2010-02-01 21:47:36
A:
I've also seen a duplicate table with a standardized prefix added to the name. All the deleted rows are moved to the duplicate table. This removes the overhead of keeping but ignoring the rows in the original table.
Jay
2010-02-01 19:51:51
A:
All actions (insert - update - delete) should be logged in a journalling table. I always log the action, timestamp and user who triggered the action. Adding an Isdelete column to the original table is bad practice.
Chris
2010-02-01 19:55:51
@Chris - It is definately not bad practice at all. Some companies mandate designs like this, no problem with a journaling table. But telling people adding an IsDeleted column is bad practice is bad practice in itself.
JonH
2010-02-01 20:19:27
Let me rephrase then. I advice against using an IsDeleted column inside the original table. For two reasons: As pointed out by another poster. It complicates your queries. Or even worse, it's possible that you forget to filter on this column, showing deleted data to your users. And second, i always try to seperate functional and technical data in my DB designs. I dont think it's a good idea to mix this up in one table.
Chris
2010-02-02 10:20:28
I forgot to mention a third reason: keeping the 'Isdeleted' records inside your original table can become a performance issue in the future (depending on # of records, # of deleted records) since most queries will filter them out. When kept in a seperate table, you can query them only when needed.
Chris
2010-02-02 11:06:04
+1
A:
If you are using SQL Server 2008, you can take advantage of the new auditing features.
RedFilter
2010-02-01 19:58:48