Sunday, January 13, 2013 2:42 AM
What is the technique to minotor update on a specific column of specific SQL table?
I.e., I have a table named "tabkle1", inside it there are columns field_1-field_n. I want to monitor the field_2. if a SQL statement "update table1 set field_2=newvalue" is executed, I will log the date/time, old and new value, and where the statement comes from(IP or machine name). Maybe to a new table or somewhere.
Sunday, January 13, 2013 5:15 AM
What version and edition of SQL are you using?? In sql server 2008 enterprise edition, you can use Change Data Capture to logged the information. Please refer http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/. However, I think this built in feature will log all the information changed (insert/update/delete) on the table.I do not think you can enable it just on one column or just for updates. Please refer to the above for more specifics.
In case, you want more customized way, you can log this info using triggers.
Please mark as 'Answer', if the solution solves your problem.
Sunday, January 13, 2013 9:22 AM
I would with OUTPUT clause or triggers.create trigger tru_MyTable on MyTable after update
if @@ROWCOUNT = 0
deleted d on d.ID = o.Id
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
- Marked As Answer by Tom LUKE Monday, January 14, 2013 4:10 PM
Sunday, January 13, 2013 10:25 AM
Trigger would be the way to go. You can get the IP address from sys.dm_exec_connection. Or the host_name() function, but the client can spoof that value.
Change Data Capture was mentioned, but it sounds like far too heavy artillery. Not the least since CDC cannot answer the question who did the change.
Erland Sommarskog, SQL Server MVP, email@example.com
- Marked As Answer by Tom LUKE Monday, January 14, 2013 4:08 PM