SQL Azure数据库变更审计：工具选型与触发器实现问询

Great question! Let's break this down into two parts: implementing a universal audit log with database triggers, and recommending low-config reporting tools that fit your needs.

Step 1: Create a Centralized Audit Table #

First, we'll build a single table to store all change logs using JSON for old/new values—no per-table custom tables required:

CREATE TABLE dbo.AuditLog (
    AuditLogId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    TableName NVARCHAR(128) NOT NULL,
    OperationType NVARCHAR(10) NOT NULL CHECK (OperationType IN ('UPDATE', 'DELETE')),
    ChangedAt DATETIME2 DEFAULT GETUTCDATE(),
    ChangedBy NVARCHAR(128) DEFAULT SUSER_SNAME(),
    OldValues NVARCHAR(MAX), -- Stores full old row as JSON
    NewValues NVARCHAR(MAX) -- Stores full new row as JSON (only for UPDATE operations)
);

Step 2: Auto-Generate Triggers for All Tables #

Instead of writing a trigger manually for each of your 10 tables, use this dynamic SQL script to automate the process. It creates triggers that log DELETE/UPDATE actions for every base table:

DECLARE @TableName NVARCHAR(128), @TriggerName NVARCHAR(256), @SQL NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'AuditLog';

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TriggerName = 'trg_' + @TableName + '_Audit';
    SET @SQL = N'
    IF EXISTS (SELECT * FROM sys.triggers WHERE name = ''' + @TriggerName + ''')
        DROP TRIGGER ' + @TriggerName + ';

    CREATE TRIGGER ' + @TriggerName + '
    ON dbo.' + @TableName + '
    AFTER UPDATE, DELETE
    AS
    BEGIN
        SET NOCOUNT ON;

        -- Log DELETE operations
        INSERT INTO dbo.AuditLog (TableName, OperationType, OldValues)
        SELECT 
            ''' + @TableName + ''',
            ''DELETE'',
            (SELECT * FROM deleted FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
        FROM deleted;

        -- Log UPDATE operations
        INSERT INTO dbo.AuditLog (TableName, OperationType, OldValues, NewValues)
        SELECT 
            ''' + @TableName + ''',
            ''UPDATE'',
            (SELECT * FROM deleted FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER),
            (SELECT * FROM inserted FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
        FROM inserted;
    END;';

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

This script loops through all your tables (excluding the audit table) and builds triggers that capture full row data as JSON—exactly what you need for a universal logging system.

Querying the Audit Log #

To search and analyze logs, use SQL's built-in JSON functions to parse stored data. For example, to filter logs for a specific table and date range, and extract a key field like Id:

SELECT 
    AuditLogId,
    TableName,
    OperationType,
    ChangedAt,
    ChangedBy,
    JSON_VALUE(OldValues, '$.Id') AS ChangedRecordId -- Replace with your table's key field
FROM dbo.AuditLog
WHERE 
    TableName = 'YourTableName'
    AND ChangedAt > DATEADD(DAY, -30, GETUTCDATE());

Here are three tools that fit your "minimal deployment" and searchable report requirements:

• Power BI Desktop

  • Totally free, no server deployment needed (just install the desktop app).
  • Connect directly to your SQL Azure database, build interactive reports with filters, search boxes, and visualizations.
  • Easily parse JSON fields using Power BI's built-in functions, and publish reports to Power BI Service for team access if needed.

• Azure Data Studio

  • Microsoft's free, cross-platform database tool.
  • Create saved queries to search the AuditLog table, then turn those queries into dashboards with charts and filters.
  • Share query notebooks with your team—no extra infrastructure required beyond connecting to your SQL Azure instance.

• SSRS (SQL Server Reporting Services)

  • If you already have an Azure VM or on-prem server, SSRS is a solid enterprise option.
  • Build parameterized reports (e.g., search by table name, date range, user) that can be shared via a web portal.
  • While it requires a bit more setup than the first two, it's still straightforward for small-scale deployments.

• Performance: Triggers add a small amount of overhead to DML operations. For large tables or high-throughput workloads, consider Azure SQL's Change Data Capture (CDC) as an alternative—but triggers are far simpler for your 10-table use case.
• Security: Restrict write access to the AuditLog table to only the database engine (via trigger permissions) to prevent tampering with logs.
• JSON Flexibility: Storing full rows as JSON means you don't have to modify the audit table when you add/remove columns from your source tables—perfect for future scalability.

内容的提问来源于stack exchange，提问作者aron