How Can I Log SQL Statements That Modify a Specific Table in SQL Server?

Creating a Trigger to Log SQL that Affected the Table

To trace the SQL statements that modify a specific table, you can create a trigger on that table. This trigger can capture the SQL query that triggered the change.

Trigger Code:

CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[TableName]
AFTER UPDATE
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = (SELECT TOP 1 text FROM sys.dm_exec_query_stats WHERE execution_count = 1 AND plan_handle = CONTEXT_INFO());
    INSERT INTO [dbo].[LogTable] (SQLText, TableName, UpdateDate) VALUES (@SQL, OBJECT_SCHEMA_NAME(OBJECT_ID), GETDATE());
END

Procedure to Execute SQL:

To execute the SQL query that triggers the trigger, create a procedure as follows:

CREATE PROCEDURE [dbo].[UpdateProcedure]
AS
BEGIN
    UPDATE [dbo].[TableName] SET ColumnName = 'UpdatedValue' WHERE Condition;
END

Using the Trigger:

After creating the trigger and procedure, you can use the following steps to log the SQL that affected the table:

1. Create a table to store the log information:

CREATE TABLE [dbo].[LogTable] (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SQLText NVARCHAR(MAX),
    TableName NVARCHAR(128),
    UpdateDate DATETIME
);

2. Execute the update procedure:

EXEC [dbo].[UpdateProcedure];

3. Check the LogTable to view the captured SQL statement and other information related to the update operation.

Alternative Approach Using XEvents:

Alternatively, you can use SQL Server Extended Events to capture the SQL statements that modify a specific table. This provides more detailed information and flexibility compared to triggers.

Setup:

1. Declare a variable to capture the execution context:

DECLARE @ContextID NVARCHAR(1024);

2. Enable XEvent tracing for sql_statement_completed events on the target table:

ALTER DATABASE [TestDB] SET ENABLE_XEVENT = ON;
EXEC sp_trace_create @trace_id = 1, @name = 'CaptureTableModifications', @destination = 4;
EXEC sp_trace_setevent 1, 24, 1;
EXEC sp_trace_setfilter 1, @columnname = 'object_id', @value = OBJECT_ID([dbo].[TableName]);

3. Start tracing:

EXEC sp_trace_start 1;

4. Perform the update operation.
5. Stop tracing:

EXEC sp_trace_stop 1;

6. Query the XEvent trace:

SELECT session_id, session_nt_address, text 
FROM sys.fn_xe_file_target_read_file('c:\Temp\trace.xel', 'c:\Temp\trace.xem', NULL, NULL);

The above is the detailed content of How Can I Log SQL Statements That Modify a Specific Table in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!