SQL Server CREATE TRIGGER: AFTER, INSTEAD OF, Audit, and DDL Examples

Support asks who changed a record and when, and the row holds only its current state. A trigger closes that gap from inside the database: it is a stored procedure that SQL Server runs automatically when an event happens, and for a data change it can read both the old and the new version of every affected row. The trigger and the statement that fired it are one transaction, so the trigger can also roll the change back. These examples run on SQL Server 2022.

What a SQL Server trigger does

SQL Server has three kinds of trigger, one per kind of event. A DML trigger runs on an INSERT, UPDATE, or DELETE against a table or a view, and it fires whenever a valid event fires, whether any rows were affected or not. A DDL trigger runs on CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements, and on system stored procedures that perform DDL-like operations. A logon trigger runs when a session is being established.

Four jobs are worth a trigger. Recording an audit trail of who changed what is the first, because a table row does not remember its own history. The second is a rule a CHECK constraint cannot express: a CHECK may reference only columns of its own table, while a trigger can read another table and reject the change on what it finds there. The third is making a view writable, which is the one case where the trigger replaces the statement instead of following it. The fourth is logging schema changes, which is what DDL triggers are for.

If the tables the trigger will watch do not exist yet, start with SQL Server CREATE TABLE.

Trigger types in SQL Server

The permission column is why most application work stays in DML triggers: ALTER on one table is a far narrower grant to ask for than CONTROL SERVER over the whole instance. Logon triggers carry an operational risk to match. They fire after authentication but before the session is established, their PRINT and error messages go to the SQL Server error log rather than back to the connection, and a logon trigger that rejects connections can lock out every user including members of sysadmin, who then have to connect through the dedicated administrator connection or start the Database Engine with the -f minimal configuration option.

AFTER vs INSTEAD OF triggers

The order around constraints is what decides which one you need. Constraints on the trigger table are checked after an INSTEAD OF trigger runs and before an AFTER trigger runs, so an AFTER trigger never sees a statement that violated a constraint, and a constraint violation rolls back what the INSTEAD OF trigger did and stops the AFTER trigger from firing at all. Write AFTER when the change should happen first and the trigger reacts to it. Write INSTEAD OF when the statement itself has to be replaced, which is the only way to make a view accept writes: an AFTER trigger cannot be defined on a view.

Two limits come with INSTEAD OF. You get one per action per object, where several AFTER triggers can share an action, and when several AFTER triggers do share one, no order is defined between them beyond the first and the last, which sp_settriggerorder sets. And INSTEAD OF UPDATE and INSTEAD OF DELETE cannot be created on a table that is the target of a cascading ON UPDATE or ON DELETE referential action.

Use inserted and deleted tables correctly

Inside a DML trigger, inserted and deleted are logical tables shaped like the table the trigger is defined on. deleted holds the old values of the affected rows and inserted holds the new ones, so an UPDATE fills both and a row appears in each.

The word to hold on to is rows, plural. One statement can change many rows, and one INSERT INTO table SELECT that inserts many rows causes a single trigger invocation, so the body has to work on a set:

INSERT INTO dbo.OrderAudit (OrderID, OldStatus, NewStatus)
SELECT i.OrderID, d.Status, i.Status
FROM inserted i
JOIN deleted d ON d.OrderID = i.OrderID;

Assigning a column to a variable does the opposite. It takes one row out of inserted with nothing to say which one, and the other rows of the same statement are never recorded:

DECLARE @OrderID bigint;
SELECT @OrderID = OrderID FROM inserted;

The other habit worth having is an early exit. A DML trigger fires even when the statement changed nothing, and holds locks while it runs, so Microsoft recommends starting each DML trigger with the two lines that release it in that case:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Audit trigger example in sqlcmd

Connect with sqlcmd:

sqlcmd -S <server_name> -U <username> -P <password>

The example records every change of an order's status, together with the login that made it and the time it happened:

CREATE TABLE dbo.Orders (
    OrderID bigint       NOT NULL PRIMARY KEY,
    Status  nvarchar(20) NOT NULL DEFAULT N'New'
);
GO

INSERT INTO dbo.Orders (OrderID) VALUES (1), (2);
GO

CREATE TABLE dbo.OrderAudit (
    AuditID      bigint IDENTITY(1,1) PRIMARY KEY,
    OrderID      bigint NOT NULL,
    OldStatus    nvarchar(20) NULL,
    NewStatus    nvarchar(20) NULL,
    ChangedBy    sysname NOT NULL,
    ChangedAt    datetime2 NOT NULL DEFAULT sysutcdatetime()
);
GO

CREATE OR ALTER TRIGGER dbo.tr_Orders_AuditStatus
ON dbo.Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    INSERT INTO dbo.OrderAudit (OrderID, OldStatus, NewStatus, ChangedBy, ChangedAt)
    SELECT i.OrderID,
           d.Status,
           i.Status,
           SUSER_SNAME(),
           sysutcdatetime()
    FROM inserted i
    JOIN deleted d ON d.OrderID = i.OrderID
    WHERE ISNULL(i.Status, '') <> ISNULL(d.Status, '');
END;
GO

One UPDATE that touches both orders produces two audit rows, because the trigger reads the whole inserted set rather than a single row:

UPDATE dbo.Orders SET Status = N'Shipped';

SELECT OrderID, OldStatus, NewStatus
FROM dbo.OrderAudit
ORDER BY OrderID;

SET NOCOUNT ON belongs at the top of a trigger that assigns variables or would otherwise send row counts back to the application. The OR ALTER clause applies to SQL Server 2016 (13.x) SP1 and later versions and replaces the drop-and-recreate pair when you redeploy the trigger.

One gap to know about before you rely on this for compliance: TRUNCATE TABLE removes rows without logging the individual deletions, so it does not activate a DELETE trigger. Users who can run it can empty a table without leaving an audit row.

INSTEAD OF trigger example on a view

An application that writes to a view needs an INSTEAD OF trigger, because a view has no other way to accept an insert that has to land in a base table with extra columns:

CREATE TABLE dbo.Customers (
    CustomerID   bigint        NOT NULL PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL,
    Email        nvarchar(255) NOT NULL,
    IsDeleted    bit           NOT NULL DEFAULT 0
);
GO

CREATE VIEW dbo.vwActiveCustomers
AS
SELECT CustomerID,
       CustomerName,
       Email
FROM dbo.Customers
WHERE IsDeleted = 0;
GO

CREATE OR ALTER TRIGGER dbo.tr_vwActiveCustomers_Insert
ON dbo.vwActiveCustomers
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Customers (CustomerID, CustomerName, Email, IsDeleted)
    SELECT CustomerID,
           CustomerName,
           Email,
           0
    FROM inserted;
END;
GO

The application inserts three columns into the view, and the base table gets the fourth:

INSERT INTO dbo.vwActiveCustomers (CustomerID, CustomerName, Email)
VALUES (1, N'Ada', N'[email protected]');

SELECT CustomerID, CustomerName, IsDeleted
FROM dbo.Customers;

Inside such a trigger, a statement against the same view does not call the trigger again: SQL Server resolves it against the base tables, and the view must then satisfy the rules for an updatable view. One more restriction is worth checking before you write the trigger, because the error arrives late: an INSTEAD OF trigger cannot be added to an updatable view defined WITH CHECK OPTION until ALTER VIEW removes that option.

DDL trigger example

A DDL trigger with database scope records schema changes as they are made. EVENTDATA returns the event as XML, and the value method reads one element out of it:

CREATE TABLE dbo.SchemaChangeLog (
    LogID       bigint IDENTITY(1,1) PRIMARY KEY,
    EventType   nvarchar(100) NOT NULL,
    CommandText nvarchar(max) NOT NULL,
    ChangedBy   sysname NOT NULL,
    EventTime   datetime2 NOT NULL DEFAULT sysutcdatetime()
);
GO

CREATE OR ALTER TRIGGER dbo.tr_LogCreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @event xml = EVENTDATA();

    INSERT INTO dbo.SchemaChangeLog (EventType, CommandText, ChangedBy, EventTime)
    SELECT @event.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
           @event.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
           SUSER_SNAME(),
           sysutcdatetime();
END;
GO

EventType holds the event that fired the trigger, CREATE_TABLE in this case. The CommandText element under TSQLCommand holds the statement that ran, so the name of the new table and its column list arrive in the log together, and SUSER_SNAME() adds the login that ran it.

Two properties of DDL triggers shape what this log contains. Local and global temporary tables raise no DDL events, so the scratch tables a report creates never reach the log. And DDL triggers are not scoped to a schema, which is why OBJECT_ID and OBJECT_NAME cannot be used to look them up; the catalog views sys.triggers and sys.trigger_events are where you check which events a trigger is registered for. When schema changes reach MERGE-based data sync workflows, that log is the record of what ran.

Manage triggers in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

A trigger is invisible in the table it fires on, which is what makes an audit trigger easy to break during a release. DbSchema reverse-engineers triggers along with the tables: the JDBC driver supplies the tables, the columns and the foreign keys, and DbSchema runs its own queries per database to read the triggers, procedures and functions with their source, as the database settings page describes. Connect through the SQL Server JDBC driver, and the trigger arrives in the .dbs model file next to the table it belongs to.

From there, the diagram shows which tables the trigger writes into, an audit table and a queue table included, so a release that changes one of them starts from a picture rather than from a search. Write and run the trigger itself in the SQL Editor, which executes against the connected database, and use Diagram → Export HTML5 or PDF Documentation to publish the tables, their descriptions and the schema through schema documentation for the reviewers who never open the database. Nearby topics: SQL Server Stored Procedures and SQL Server Transactions.

Pick AFTER or INSTEAD OF from where the constraint checks fall, write the body against the whole inserted set, and return early when nothing changed. To see the triggers you already have next to the tables they fire on, download DbSchema at https://dbschema.com/download.html and reverse-engineer your SQL Server database: connecting, the diagram and the SQL Editor are in the free Community edition, and exporting the documentation is a Pro edition feature.

FAQ

Do SQL Server triggers fire once per row?

Microsoft recommends writing the body as rowset logic rather than as a cursor, because one INSERT INTO ... SELECT causes a single trigger invocation and leaves every affected row in inserted.

How do I temporarily disable a trigger?

Run DISABLE TRIGGER dbo.tr_Orders_AuditStatus ON dbo.Orders; and re-enable it later with ENABLE TRIGGER. Disabling does not drop the trigger, and running ALTER TRIGGER on it enables it again, which is easy to do by accident during a redeploy.

Should triggers replace application logic?

A constraint is the first choice, because PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY state the rule in the table definition and SQL Server enforces it with no code of yours to maintain. Microsoft's guidance is that DML triggers are most useful where constraints cannot meet the need, for example a rule that has to read another table or an error message the application has to understand.