SQL Server CREATE TRIGGER – AFTER, INSTEAD OF, Audit, and DDL Examples | DbSchema

Table of Contents

1. What a SQL Server trigger does
2. Trigger types in SQL Server
3. AFTER vs INSTEAD OF triggers
4. Use inserted and deleted tables correctly
5. Audit trigger example in sqlcmd
6. INSTEAD OF trigger example on a view
7. DDL trigger example
8. Manage triggers in DbSchema
9. FAQ
10. Conclusion

SQL Server triggers are database objects that run automatically when a defined event occurs. They are useful for audit logging, enforcing business rules close to the data, and reacting to DML or DDL changes.

They can also be misused. Triggers add hidden behavior to writes, so the best trigger designs are set-based, documented, and narrowly focused. DbSchema helps because you can keep the trigger logic, related tables, and documentation visible together instead of scattering the behavior across scripts only.

What a SQL Server trigger does

A trigger executes automatically after or instead of an event such as:

• INSERT
• UPDATE
• DELETE
• CREATE_TABLE
• other schema-level events

Common enterprise use cases include:

• audit trails for sensitive tables
• validating rules that are hard to express with a simple constraint
• protecting updateable views
• recording schema changes for compliance

If you are still designing the tables that triggers will target, start with SQL Server CREATE TABLE.

Trigger types in SQL Server

For most application work, DML triggers matter the most, especially AFTER and INSTEAD OF triggers.

AFTER vs INSTEAD OF triggers

Use AFTER when the base change should happen first and the trigger should react to it. Use INSTEAD OF when you want to intercept the requested action and replace it with custom logic.

Use inserted and deleted tables correctly

Inside SQL Server DML triggers, the pseudo-tables inserted and deleted hold the affected rows.

• inserted contains new values
• deleted contains previous values

The most important best practice is to write set-based logic. SQL Server triggers fire once per statement, not once per row, so a single update can place many rows into inserted and deleted.

That means this is a good pattern:

INSERT INTO dbo.AuditTable (...)
SELECT ...
FROM inserted i
JOIN deleted d ON d.OrderID = i.OrderID;

And this is a risky pattern:

-- bad idea: assumes a single row
SELECT @OrderID = OrderID FROM inserted;

Audit trigger example in sqlcmd

The audit example below records order status changes.

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;

    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

This is a common enterprise pattern because support teams often need to know who changed a status and when the change happened.

INSTEAD OF trigger example on a view

INSTEAD OF triggers are especially useful when the application writes to a view.

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

This pattern lets you expose a simpler application-facing surface while still controlling how data lands in the base table.

DDL trigger example

Schema auditing is another strong commercial use case.

CREATE TABLE dbo.SchemaChangeLog (
    LogID        bigint IDENTITY(1,1) PRIMARY KEY,
    EventType    nvarchar(100) NOT NULL,
    ObjectName   nvarchar(255) NULL,
    LoginName    nvarchar(255) 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;

    INSERT INTO dbo.SchemaChangeLog (EventType, ObjectName, LoginName, EventTime)
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
           EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
           EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(255)'),
           sysutcdatetime();
END;
GO

If you rely on DDL automation or MERGE-based data sync workflows, documenting these trigger side effects is critical.

Manage triggers in DbSchema

DbSchema helps when triggers are part of a wider data model rather than isolated SQL snippets.

Typical workflow:

1. connect through the SQL Server JDBC driver
2. inspect the base tables, views, and foreign keys in the diagram
3. write or test trigger code in the SQL editor
4. document audit and business-rule behavior with schema documentation
5. deploy related object changes through schema synchronization

DbSchema is especially helpful when a trigger touches multiple tables such as an audit table, a queue table, or a summary table. The dependencies remain visible, which reduces accidental side effects during releases. For nearby topics, see SQL Server Stored Procedures and SQL Server Transactions.

FAQ

Do SQL Server triggers fire once per row?

No. SQL Server triggers fire once per statement. Always assume inserted and deleted can contain multiple rows.

When should I use an INSTEAD OF trigger?

Use it when you want to intercept a write and replace it with custom logic, most commonly on views.

Are triggers bad for performance?

They can slow writes if they do too much work. Keep them focused, set-based, and well indexed, and avoid hidden network or long-running operations inside the trigger body.

How do I temporarily disable a trigger?

Use DISABLE TRIGGER trigger_name ON dbo.TableName; and re-enable it later with ENABLE TRIGGER.

Should triggers replace application logic?

Usually no. Triggers are best for rules that truly belong in the database layer, such as auditing or protecting data integrity. Business workflows that need complex orchestration are often clearer in application code or procedures.

Conclusion

SQL Server CREATE TRIGGER is most valuable when you need reliable auditing, controlled view updates, or schema-event logging close to the data. The strongest trigger designs handle multi-row changes correctly, stay focused, and make side effects easy to understand.

DbSchema complements that approach by keeping the trigger, the affected objects, and the documentation in one reviewable workflow.