Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Triggers are the do-it-yourself way to record changes. A DDL trigger records schema changes (CREATE, ALTER, DROP); a DML trigger records data changes on one table. Prefer SQL Server Audit for who-did-what and temporal tables for row history. Use triggers when you need something those cannot do, such as storing the change in a table the application team can query.

Step 1: A table for schema changes

USE [YourDB];
CREATE TABLE dbo.DDLAudit (
    AuditID     int IDENTITY(1,1) PRIMARY KEY,
    EventTime   datetime2(0)   NOT NULL DEFAULT SYSDATETIME(),
    LoginName   sysname        NOT NULL,
    HostName    nvarchar(128)  NULL,
    EventType   nvarchar(100)  NULL,
    SchemaName  sysname        NULL,
    ObjectName  sysname        NULL,
    TSQLCommand nvarchar(max)  NULL,
    EventData   xml            NOT NULL
);
GRANT INSERT ON dbo.DDLAudit TO public;

The GRANT matters. The trigger runs as the person making the change, and if their insert into the audit table fails, their DDL is rolled back.

Step 2: The database DDL trigger

USE [YourDB];
GO
CREATE TRIGGER trg_DDLAudit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @e xml = EVENTDATA();
    INSERT dbo.DDLAudit (LoginName, HostName, EventType, SchemaName, ObjectName, TSQLCommand, EventData)
    VALUES (ORIGINAL_LOGIN(), HOST_NAME(),
            @e.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
            @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
            @e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
            @e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
            @e);
END;
GO

For server-wide events such as logins being created or dropped, use ON ALL SERVER with an event group such as DDL_LOGIN_EVENTS, and write to a table in your DBA database.

Step 3: A DML trigger for one table

This stores the old and new values of each changed row as JSON (SQL Server 2016 onwards). Replace YourKeyColumn, Col1 and Col2 with the table’s own columns.

USE [YourDB];
CREATE TABLE dbo.YourTable_Audit (
    AuditID   bigint IDENTITY(1,1) PRIMARY KEY,
    AuditTime datetime2(0)  NOT NULL DEFAULT SYSDATETIME(),
    LoginName sysname       NOT NULL DEFAULT ORIGINAL_LOGIN(),
    Operation char(1)       NOT NULL,       -- I, U or D
    KeyValue  int           NOT NULL,
    OldData   nvarchar(max) NULL,
    NewData   nvarchar(max) NULL
);
GO
CREATE TRIGGER trg_YourTable_Audit ON dbo.YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
        RETURN;
    INSERT dbo.YourTable_Audit (Operation, KeyValue, OldData, NewData)
    SELECT CASE WHEN i.YourKeyColumn IS NULL THEN 'D'
                WHEN d.YourKeyColumn IS NULL THEN 'I'
                ELSE 'U' END,
           COALESCE(i.YourKeyColumn, d.YourKeyColumn),
           CASE WHEN d.YourKeyColumn IS NOT NULL THEN
               (SELECT d.YourKeyColumn, d.Col1, d.Col2 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) END,
           CASE WHEN i.YourKeyColumn IS NOT NULL THEN
               (SELECT i.YourKeyColumn, i.Col1, i.Col2 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) END
    FROM inserted i
    FULL OUTER JOIN deleted d ON d.YourKeyColumn = i.YourKeyColumn;
END;
GO

What triggers exist

USE [YourDB];
SELECT OBJECT_NAME(parent_id) AS table_name, name AS trigger_name,
       is_disabled, is_instead_of_trigger, parent_class_desc
FROM sys.triggers;

SELECT name, is_disabled FROM sys.server_triggers;

Watch out for

• A trigger runs inside the user’s transaction. A slow audit insert slows every change, and a failed one rolls the change back.
• Bulk loads skip triggers unless they use FIRE_TRIGGERS.
• Someone with enough rights can disable the trigger. A DDL trigger can record that, but it cannot prevent it.
• Audit tables grow forever unless you purge them. Agree a retention period with the data owner.

From my SQL Server DBA handbook.