Here I am using SQL Audit functionality to track updates happening to a table in SQL Server.
SQL Server audit uses Extended Events to help create an audit.
— setting server audit
USE master
GO
CREATE SERVER AUDIT [audit_Table_updates]
TO FILE
( FILEPATH = N’C:\Users\gollapudiy\Desktop\Delete’
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
— configuring database audit
USE [DWConfiguration]
GO
CREATE DATABASE AUDIT SPECIFICATION [Table_customers]
FOR SERVER AUDIT [audit_Table_updates]
ADD (UPDATE ON OBJECT::[customers] BY [public])
GO
—- enabling server and database audit / by default both will be in disable state after creation
USE [master]
GO
ALTER SERVER AUDIT [audit_Table_updates] WITH (STATE = ON);
USE [DWConfiguration]
GO
ALTER DATABASE AUDIT SPECIFICATION [Table_customers] WITH (STATE = ON);
GO
— updating the table for test
update customers set name = ‘yogi2’
— reading audit file data
SELECT * FROM fn_get_audit_file(‘C:\Users\gollapudiy\Desktop\Delete*’,default,default)
— disabling the audits
USE [master]
GO
ALTER SERVER AUDIT [audit_Table_updates] WITH (STATE = OFF);
USE [DWConfiguration]
GO
ALTER DATABASE AUDIT SPECIFICATION [Table_customers] WITH (STATE = OFF);
GO
—–Reference
selectstarfromyogi 