How Can I Log the SQL Statements Affecting a Specific Table?

Creating a Trigger to Log SQL Affecting a Table

When faced with a situation where the source of table updates is unknown, it becomes essential to identify the culprit. To achieve this, you can create a trigger on the affected table to capture the executed SQL that triggered the update. This article demonstrates two approaches to this problem: using a straightforward SQL trigger and a more comprehensive solution with SQL Server 2008 extended events.

SQL Trigger Method:

Create a trigger on the table in question that executes a stored procedure to record the SQL statement that triggered the update.

CREATE TRIGGER [triggerName] ON [tableName]
AFTER UPDATE
AS
BEGIN
    EXEC [storedProcedureName]
END

In the stored procedure, log the SQL statement using sp_WhoIsActive:

CREATE PROCEDURE [storedProcedureName]
AS
BEGIN
    DECLARE @sql_statement NVARCHAR(MAX)

    EXEC sp_WhoIsActive @output_text = @sql_statement OUTPUT
END

Extended Events Method (SQL Server 2008):

This method involves creating an extended events session that captures details about SQL statement execution.

CREATE EVENT SESSION [sessionName] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (object_id = ' + CAST(OBJECT_ID('[affectedTable]') AS VARCHAR(10)) + ')
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'path\to\filename.xel', metadatafile = 'path\to\filename.xem')
ALTER EVENT SESSION [sessionName] ON SERVER STATE = START

After executing the update, stop the session and parse the captured data to retrieve the SQL statement.

EXEC sp_executesql 'ALTER EVENT SESSION [sessionName] ON SERVER STATE = STOP'

SELECT CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('path\to\filename.xel', 'path\to\filename.xem', NULL, NULL)

Join the results with DMVs to retrieve additional information about the execution:

WITH CapturedResults AS
(
    ...
)
,
StackData AS
(
    ...
)
SELECT ...
FROM StackData AS sd ...

These approaches provide a way to identify the SQL statements that are affecting the table, allowing you to investigate further and resolve any issues.

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