CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 5499

What is the best way to track when table(s) are updated in SQL?

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!

Upvotes: 3

Views: 4826

Answers (7)

user631662
user631662

Reputation: 73

If you are using SQL 2008 then you can use CDC(Change Data Capture) for the tracking.

The below link gives the full details. If you are enabling the cdc for particular table then automatically delete data's will be collected.

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

If you are using SQL Server 2008, you can take advantage of the new auditing features.

Upvotes: 1

Chris
Chris

Reputation: 124

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.

Upvotes: 0

Jay
Jay

Reputation: 14441

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.

Upvotes: 0

aaaa bbbb
aaaa bbbb

Reputation: 3043

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.

Upvotes: 6

JonH
JonH

Reputation: 33143

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.

Upvotes: 2

Pentium10
Pentium10

Reputation: 207828

Flag the records as deleted=1 and do not delete it. Do a trigger on delete instead update...

Upvotes: 0

Related Questions