What is the technique to minotor update on a specific column of specific SQL table?

Question

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.

Answer 1

Hello Tom,

   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.  

Refer http://bytes.com/topic/sql-server/answers/81617-sql-trigger-changed-columns

 http://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/   Hope it helps!!

---

Please mark as 'Answer', if the solution solves your problem.

Answer 2

I would with OUTPUT clause or triggers.

create trigger tru_MyTable on MyTable after update
as

if @@ROWCOUNT = 0
    return

insert MyAuditTable
select
    i.ID
,   d.MyColumn
,   i.MyColumn
from
    inserted    i
join
    deleted    d    on    d.ID    = o.Id
go

---

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

Answer 3

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, [e-mail]