How to track modification date on specific table

Question

How to track modification date on specific table

WhiteLotus

SSChampion

Points: 12122
More actions
July 13, 2015 at 8:23 pm

#305698

Hi all,
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
I really appreciate any idea / feedback
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

If all you want to know is when was a row changed are you able to add a new column to the table called something like DateModified and have a default set of GETDATE() for the column.

Alternatively creating a trigger on the table to capture the changes to a new table is an option, which is similar(ish) to what CDC would be doing.

MCITP SQL 2005, MCSA SQL 2012

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

RTaylor2208 (7/14/2015)
If all you want to know is when was a row changed are you able to add a new column to the table called something like DateModified and have a default set of GETDATE() for the column.

That'll tell the insert date, not the modification date. Updating a row doesn't apply defaults.

To get the date a row was modified requires an update trigger (or CDC, change tracking, etc)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

Very good point on the default, that totally slipped my mind.

MCITP SQL 2005, MCSA SQL 2012

ScottPletcher SSC Guru Points: 101134 More actions · Answer 4

For a particular table, you can add a trigger to the table to capture the relevant details from any UPDATE statements.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

WhiteLotus SSChampion Points: 12122 More actions · Answer 5

Guys thanks so much for the feedback !!

Really appreciate it !!!

cheers