Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table.

Due to some specific reasons I have to use change tracking as I was told not to use triggers and Change Data Capture.

Right now I am able to fetch current data and which column is modified. But in addition I want to track time of modification and old data.

But if anyone can help me to get any one of these two will be very helpful for me.

share|improve this question
3  
Without triggers or CDC? Ho hum. Stored procedures allowed...? – gbn Feb 3 '12 at 13:02
Yes we can use stored procedure but we are not maintaining any extra table for any input – user1166764 Feb 3 '12 at 13:13
You can find some more additional information from my Article codeproject.com/Articles/537649/… – Chandra Sekhar Feb 5 at 9:22

migrated from stackoverflow.com Feb 3 '12 at 17:29

1 Answer

up vote 2 down vote

If you can't add any extra storage for the data (as noted in your comment) then you simply can't do this. SQL Server doesn't track the old and modified rows for you.

There's a duct-tape way of doing it temporarily: you can read the transaction log. When combined with the full chain of backups, you can build the old & new data together. Third party data recovery products like Quest LiteSpeed do this - for any transaction, they'll show you the old and new data by reading through the full backup and transaction log backups.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.