History of actions that occured on a table in SQL Server

Question

I am running a shell script that runs insert, update, delete queries on a table in an SQL Server DB. Is there a way to get a history of all transactions that happened on the table to validate that they occurred as expected. That is the number of updates, inserts and deletes that happened. And any errors that could have occurred.

Answer 1

Here's some out-of-the-box features of SQL Server that can help you accomplish your goal. You'll need to proactively enable / set one of them up before they collect the data you need.

For reference:

1. Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

2. Temporal Tables - System versioned copies of the user defined tables that track changes.

3. Change Tracking - Automated tracking of changes for your user defined tables.

4. Change Data Capture - Keeps track of DML changes to the specified user defined tables.

5. Audit - Automatically tracks a multitude of actions at the server level.

Answer 2

First of all I should mention that if transaction is applied, you may expect correct result. It is C in ACID.

Maybe transaction log is the feature that you are looking for. Please find a quick guide how to interact with it here.

Answer 3

You can try enabling tracing:

INSERT, UPDATE, DELETE operations will be saved in the file. I note that a lot of things that you don't need will be saved in the file. This is, after all, a diognastic tool.

Please note that the MSSQL user must have write access to this directory (C:\Temp\SampleTrace)

DECLARE @RC int, @TraceID int, @on BIT  
EXEC @rc = sp_trace_create @TraceID output, 0, N'C:\Temp\SampleTrace'  
  
-- Select the return code to see if the trace creation was successful.  
SELECT RC = @RC, TraceID = @TraceID  
  
-- Set the events and data columns you need to capture.  
SELECT @on = 1  
  
-- 10 is RPC:Completed event. 1 is TextData column.   
EXEC sp_trace_setevent @TraceID, 10, 1, @on   
-- 13 is SQL:BatchStarting, 11 is LoginName  
EXEC sp_trace_setevent @TraceID, 13, 11, @on   
-- 13 is SQL:BatchStarting, 14 is StartTime  
EXEC sp_trace_setevent @TraceID, 13, 14, @on   
-- 12 is SQL:BatchCompleted, 15 is EndTime  
EXEC sp_trace_setevent @TraceID, 12, 15, @on   
-- 13 is SQL:BatchStarting, 1 is TextData  
EXEC sp_trace_setevent @TraceID, 13, 1, @on   
  
-- Set any filter. Not provided in this example  
--EXEC sp_trace_setfilter 1, 10, 0, 6, N'%Profiler%'  
  
-- Start Trace (status 1 = start)  
EXEC @RC = sp_trace_setstatus @TraceID, 1  
GO