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.
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.
Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.
Temporal Tables - System versioned copies of the user defined tables that track changes.
Change Tracking - Automated tracking of changes for your user defined tables.
Change Data Capture - Keeps track of DML changes to the specified user defined tables.
Audit - Automatically tracks a multitude of actions at the server level.
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