List Columns Updated(By Triggers) in the last 24 hrs

Question

List Columns Updated(By Triggers) in the last 24 hrs

vinu512

SSCoach

Points: 15729
More actions
June 14, 2012 at 6:33 am

#392520

We have a situation where the Data we have was modified in the last 24hrs. I have come to the conclusion that some Triggers functioning in the Database have updated some Tables which have caused the change in the Data.
Is there a way I can List all List Columns from all tables Updated(By Triggers) in the last 24 hrs?
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

No, SQL Server doesn't track such things.

You will need to review your triggers (you can use handy Search add-on from Redgate) and find which one do update your columns, you may need to add additional code to trace which triggers create a problem for you.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 2

- it's all in your db log, but it may take some effort to get it out ... or a vendor tool.

- Is this a one time operation or is your goal continuous follow up ?

If the latter, maybe it's time to investigate CDC.

Check out the series Roy Ernest[/url] published at SSC

http://www.sqlservercentral.com/articles/Auditing/75790/

http://www.sqlservercentral.com/articles/SQL+Server/77040/

http://www.sqlservercentral.com/articles/Auditing/90178/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vinu512 SSCoach Points: 15729 More actions · Answer 3

Right now it is supposed to be a one time affair but continuous Follow up is also on my mind so that I can double check sometimes and make sure nothing of this sort happens again.

Thanks for the advice Johan. I'll have a look at CDC and also see where I can get with the Logs.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

vinu512 SSCoach Points: 15729 More actions · Answer 4

I am really sorry guyz.....I forgot to mention that our server is SQL Server 2005.

I am really sorry to have posted in the wrong Section.

Johan, your solution of CDC was introduced in SQL Server 2008. So, I would have to stick to analyzing the Log.

Still, please update me if there is something close to CDC in Sql Server 2005.