Different Types of Triggers in SQL Server

Estimated study time: 10 minutes. Know which trigger to reach for and when.

A trigger is a special kind of stored procedure that runs automatically in response to an event on a table, view, or the database itself. Triggers are grouped by the event that fires them.

1. DML Triggers

DML (Data Manipulation Language) triggers fire on INSERT, UPDATE, or DELETE statements against a table or view. They come in two flavors:

• AFTER triggers - run after the triggering action completes and the row has already been written.
• INSTEAD OF triggers - run in place of the triggering action, so you decide what actually happens.

CREATE TRIGGER trg_AfterInsert_Employees
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'A new employee row was added.';
END;

2. DDL Triggers

DDL (Data Definition Language) triggers fire on schema-changing statements such as CREATE, ALTER, and DROP. They're commonly used for auditing structural changes or blocking accidental schema edits.

CREATE TRIGGER trg_PreventDrop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Dropping tables is not allowed on this database.';
    ROLLBACK;
END;

3. Logon Triggers

Logon triggers fire in response to a LOGON event, right after a user session is authenticated but before it reaches the server. They're useful for auditing logins or restricting sessions based on conditions like time of day or origin.

CREATE TRIGGER trg_LogonAudit
ON ALL SERVER
FOR LOGON
AS
BEGIN
    INSERT INTO LoginAudit(LoginName, LoginTime)
    VALUES (ORIGINAL_LOGIN(), GETDATE());
END;

Choosing the Right Trigger