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

Creating Triggers to Log Affected SQL in SQL Server

In SQL Server 2008, you can create triggers to capture the SQL that modifies a table and log it for auditing purposes.

Trigger Definition:

CREATE TRIGGER [dbo].[triggerAfterUpdate] 
   ON  [dbo].[TableWithMysteryUpdate] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[LogTable] (
        ModifiedDate,
        ModifyingSQL
    )
    VALUES (
        GETDATE(),
        EVENTDATA()
    );
END

Log Table Definition:

CREATE TABLE [dbo].[LogTable] (
    [LogID] [INT] NOT NULL IDENTITY(1, 1),
    [ModifiedDate] [DATETIME] NOT NULL,
    [ModifyingSQL] [NVARCHAR](MAX) NOT NULL
);

Example Usage:

After creating the trigger, any updates to the [dbo].[TableWithMysteryUpdate] table will be logged in the [dbo].[LogTable].

Additional Notes:

• The EVENTDATA() function captures the executed SQL statement that triggered the event.
• The GETDATE() function records the date and time of the update.
• You can customize the log table to include additional information such as the user who made the update or the affected rows.
• This approach provides visibility into the SQL that modifies specific tables without requiring extensive database knowledge.

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