Types of Triggers in SQL: Your Roadmap to Database Control

In 1988, when Microsoft joined hands with Ashton-Tate and Sybase, it led to the evolution of SQL Server. This was a big development in the database market. Since then, SQL has been updated regularly to include numerous features. One such feature of SQL is triggers. This blog illustrates what a trigger is, different types of triggers in SQL, their syntax, and examples.

What is a Trigger in SQL?

A trigger is a piece of procedural code in a database system executed only when a given event happens at the database or instance level. Different types of events, such as the insertion of a row in a table, changes in the structure of a table, etc., can fire a trigger. Triggers are different from stored procedures as a user cannot manually execute them, receive parameters, and commit or roll back a transaction inside it. You can further learn and enhance your knowledge about triggers in SQL by pursuing a SQL course.

3 Different Types of Triggers in SQL

There are three types of triggers in SQL servers. 

1. DML or Data Manipulation Language Triggers

DML triggers allow the user to execute an additional code in response to the data modification or execution of insert, update, or delete statements.

2. DDL or Data Definition Language Triggers

DDL triggers allow the user to execute code in response to changes in the structure of the database (dropping or creating a table) or a server event (user login). Based on where they are scoped, they are divided into two types, Database Scoped DDL triggers and Server Scoped DDL triggers. 

3. Logon Triggers

Logon triggers are specific server-scoped DDL triggers that fire in response to a LOGON event raised when the session of the user is established. 

Now, let’s look at each of these triggers in detail.

Want to start your career in data analysis? Join our data analyst course with placement and get a Job Guarantee.

1. DML Triggers

DML triggers run automatically in response to DML events and are not performed manually by the users. Note that a DML trigger can be associated with only a single table or view but can be associated with multiple DML events. 

Here is the syntax to create a DML trigger.

CREATE TRIGGER trigger_name   
ON { Table name or view name }   
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }

In the above code, the FOR, AFTER, and INSTEAD OF arguments are used to indicate when the trigger must fire. The FOR and AFTER arguments occur after the insert, update, or delete operation. The INSTEAD OF argument occurs in place of insert, update, or delete operation. INSERT, UPDATE, and DELETE are DML events that cause the trigger to fire. 

The DML triggers can be classified into two types, AFTER triggers and INSTEAD OF triggers.

a) AFTER Triggers

The AFTER trigger fires when the triggering action that fired it is completed successfully. It is further divided into three types.

Syntax:

CREATE TRIGGER schema_name.trigger_name  
ON table_name  
AFTER {INSERT | UPDATE | DELETE}  
AS  
   BEGIN  
     -- Trigger Statements  
                 -- Insert, Update, Or Delete Statements  
              END

AFTER INSERT Trigger

The AFTER INSERT trigger allows the user to perform additional actions or execute specific logic immediately after an INSERT operation has been completed successfully. 

Here is an example of the use of this trigger. In the following code, two tables, ‘Employees’ and ‘EmployeeAudit’ are created. The first one stores the information of employees and the second tracks the audit trail of any new employee insertions.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- Create the EmployeeAudit table to track the audit trail
CREATE TABLE EmployeeAudit (
    AuditID INT PRIMARY KEY,
    Action VARCHAR(50),
    EmployeeID INT,
    InsertedDateTime DATETIME
);

-- Create the AFTER INSERT trigger
CREATE TRIGGER tr_AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
    -- Insert the audit record into the EmployeeAudit table
    INSERT INTO EmployeeAudit (Action, EmployeeID, InsertedDateTime)
    SELECT 'INSERT', EmployeeID, GETDATE()
    FROM inserted;
END;

The AFTER INSERT trigger, ‘tr_AfterInsertEmployee’, will automatically fire when a new record is inserted in the table ‘Employees’. The trigger will insert an audit record into the table ‘EmployeeAudit’. It will record the ‘INSERT’ action that occurred, the ‘EmployeeID’ of the new employee added, and the current date and time. 

Note that ‘inserted’ is the pseudo table that contains rows affected by ‘INSERT’ operations. It is a temporary table that mirrors the structure of the table owned by the trigger.

AFTER UPDATE Trigger

The AFTER UPDATE trigger allows the user to perform additional actions or execute specific logic immediately after an UPDATE operation has successfully modified the data. 

The following example will help in tracking the price change of a product over time and maintain an audit trail for the same.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- Create the EmployeeAudit table to track the audit trail
CREATE TABLE EmployeeAudit (
    AuditID INT PRIMARY KEY,
    Action VARCHAR(50),
    EmployeeID INT,
    InsertedDateTime DATETIME
);

-- Create the AFTER INSERT trigger
CREATE TRIGGER tr_AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
    -- Insert the audit record into the EmployeeAudit table
    INSERT INTO EmployeeAudit (Action, EmployeeID, InsertedDateTime)
    SELECT 'INSERT', EmployeeID, GETDATE()
    FROM inserted;
END;

In the above code, we created two tables, ‘Products’ and ‘ProductPriceAudit’. Now when the ‘Price’ column of a product is updated in the ‘Products’ table, the trigger, ‘tr_AfterUpdateProductPrice’ is fired. It checks if the product price was updated using the ‘IF UPDATE’ condition. If the price was updated, then a record is inserted into the table, ‘ProductPriceAudit’. The record will include ‘ProductID’, the old price, the new price, and the current date and time when the update occurred.

AFTER DELETE Trigger

The AFTER DELETE trigger allows the user to perform additional actions or execute specific logic immediately after a DELETE operation has successfully removed the data.

The following example will demonstrate how the AFTER DELETE trigger can be used to create an audit trail to keep track of any deleted records.

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

-- Create the DeletedOrdersAudit table to track the audit trail for deleted orders
CREATE TABLE DeletedOrdersAudit (
    AuditID INT PRIMARY KEY,
    OrderID INT,
    CustomerName VARCHAR(100),
    OrderDate DATE,
    DeletedDateTime DATETIME
);

-- Create the AFTER DELETE trigger
CREATE TRIGGER tr_AfterDeleteOrder
ON Orders
AFTER DELETE
AS
BEGIN
    -- Insert the audit record into the DeletedOrdersAudit table
    INSERT INTO DeletedOrdersAudit (OrderID, CustomerName, OrderDate, DeletedDateTime)
    SELECT OrderID, CustomerName, OrderDate, GETDATE()
    FROM deleted;
END;

In the above code, the trigger ‘tr_AfterDeleteOrder’, will automatically fire when a row is deleted from the ‘Orders’ table. The trigger will capture the information of the deleted record and insert an audit record in the table, ‘DeletedOrdersAudit’.

b) INSTEAD OF Triggers

The INSTEAD OF trigger fires before the triggered operation is executed. It fires even if the constraint check fails. It is further divided into three types. 

Syntax:

CREATE TRIGGER schema_name.trigger_name  
ON table_name  
INSTEAD OF {INSERT | UPDATE | DELETE}  
AS  
   BEGIN  
      -- trigger statements  
      -- Insert, Update, or Delete commands  
   END

INSTEAD OF INSERT Trigger

The INSTEAD OF INSERT trigger allows the user to perform custom actions on the data before inserting it into the target table. 

The following example will help you better understand the function of this trigger.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Create the INSTEAD OF INSERT trigger
CREATE TRIGGER tr_InsteadOfInsertEmployee
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    -- Perform custom logic before the actual insert
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
    SELECT EmployeeID, FirstName, LastName, 
           CASE 
               WHEN Salary < 0 THEN 0  -- Ensure that Salary cannot be negative
               ELSE Salary 
           END
    FROM inserted;
END;

In the above code, a table ‘Employees’ is created. When we try to insert a new record, the trigger ‘tr_InsteadOfInsertEmployee’ will be fired instead of the default insert operation. The trigger will check the ‘Salary’ column. If there is a negative value, it will convert it to zero. If the ‘Salary’ value is positive, then the record will be inserted with the original ‘Salary’ value. It will ensure there are no negative salary values inserted into the table.

Also Read: Different Types of SQL Commands

INSTEAD OF UPDATE Trigger

The INSTEAD OF UPDATE trigger allows the user to apply custom logic to the data before updating the target table. 

Through the below-mentioned example, we will create two tables ‘Employees’ and ‘EmployeeAudit’. Here, we will prevent direct updates of the ‘Salary’ column in the ‘Employees’ table and instead, store the old and new salary values in the ‘EmployeeAudit’ table.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Create the EmployeeAudit table to track the audit trail for updates
CREATE TABLE EmployeeAudit (
    AuditID INT PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    UpdatedDateTime DATETIME
);

-- Create the INSTEAD OF UPDATE trigger
CREATE TRIGGER tr_InsteadOfUpdateSalary
ON Employees
INSTEAD OF UPDATE
AS
BEGIN
    -- Insert the audit record into the EmployeeAudit table
    INSERT INTO EmployeeAudit (EmployeeID, OldSalary, NewSalary, UpdatedDateTime)
    SELECT d.EmployeeID, d.Salary AS OldSalary, i.Salary AS NewSalary, GETDATE()
    FROM inserted i
    JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

The ‘tr_InsteadOfUpdateSalary’ trigger will capture the old and new salary values from the ‘inserted’ and ‘deleted’ pseudo tables that hold the updated and original values. Then, it will insert an audit record in the ‘EmployeeAudit’ table with ‘EmployeeID’, old salary, new salary, and the current date and time when the update happened.

INSTEAD OF DELETE Trigger

The INSTEAD OF DELETE trigger allows the user to apply custom logic or perform certain actions before deleting the data from the target table. 

The following example will show how instead of deleting the record completely, it is deleted from a table and inserted into another one using the INSTEAD OF DELETE trigger.

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

-- Create the DeletedOrders table to store deleted orders for auditing
CREATE TABLE DeletedOrders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE,
    DeletedDateTime DATETIME
);

-- Create the INSTEAD OF DELETE trigger
CREATE TRIGGER tr_InsteadOfDeleteOrder
ON Orders
INSTEAD OF DELETE
AS
BEGIN
    -- Move the deleted order to the DeletedOrders table
    INSERT INTO DeletedOrders (OrderID, CustomerName, OrderDate, DeletedDateTime)
    SELECT OrderID, CustomerName, OrderDate, GETDATE()
    FROM deleted;
END;

With the above code, we have created two tables, ‘Orders’ and ‘DeletedOrders’. When we try to delete a record, the trigger ‘tr_InsteadOfDeleteOrder’ will activate instead of the default delete operation. The trigger will capture the deleted record from the ‘deleted’ pseudo table, which holds the rows to be deleted and insert the information into the ‘DeletedOrders’ table.

2. DDL Triggers

The DDL triggers are fired by a DDL event, such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements. It can also be fired in response to certain system-defined stored procedures. They can be written using both Transact SQL and CLR code. They are used when we have to prevent, audit, or respond to a change in the database schema. 

To create a DML trigger we use the CREATE TRIGGER statement. Here is the syntax for the same.

CREATE TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH [ENCRYPTION | EXECUTE AS Clause] ]  
{ FOR | AFTER } { DDL event } 
AS 
{ Your code goes here }

Here, ALL SERVER and DATABASE set the scope of the trigger. ENCRYPTION encrypts the trigger definition in the metadata. EXECUTE AS allows to change the security context on which the trigger runs. Either of the arguments (FOR and AFTER) can be used before specifying the DDL event that will cause the trigger to fire.

The following is an example where the DDL trigger will prevent the dropping of a table with a specific naming pattern.

-- Create a DDL trigger to prevent dropping tables with a specific naming pattern
CREATE TRIGGER tr_PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    DECLARE @TableName NVARCHAR(128);
    SET @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');

    IF @TableName LIKE 'AuditTable%'
    BEGIN
        PRINT 'Tables with names starting with "AuditTable" cannot be dropped.';
        ROLLBACK;
    END;
END;

In the above code, the trigger ‘ tr_PreventDropTable’ is created on the entire database and it fires when the ‘DROP_TABLE’ event occurs. EVENTDATA() function is used to access information about the event that triggered the trigger. Then, the trigger checks if the table name starts with the pattern ‘AuditTable’. If it matches, it displays a message using ‘PRINT’ and rolls back the ‘DROP_TABLE’  operation using ‘ROLLBACK’. Therefore, the table is prevented from being dropped.

3. Logon Triggers

Though Logon triggers are fired in response to logon events, they do not execute on unsuccessful logon attempts. They are used to audit and control server sessions such as tracking login activity and limiting the number of sessions for particular logins. Transact SQL security functions and a set of dynamic management views are used to obtain login information. 

The following is an example of creating a logon trigger.

-- Create the audit log table to store successful logon events
CREATE TABLE LogonAudit (
    LogonID INT PRIMARY KEY IDENTITY(1,1),
    LoginName NVARCHAR(128),
    LogonTime DATETIME,
    ClientIP NVARCHAR(50)
);

-- Create the logon trigger
CREATE TRIGGER tr_LogonAudit
ON ALL SERVER
FOR LOGON
AS
BEGIN
    INSERT INTO LogonAudit (LoginName, LogonTime, ClientIP)
    VALUES (ORIGINAL_LOGIN(), GETDATE(), HOST_NAME());
END;

The above code will create a table ‘LogonAudit’ to store the audit log for successful logon events. It has columns, ‘LoginID’, ‘LoginName’ to store the name of the user who has logged in, ‘LogonTime’ to record the timestamp of the logon event, and ‘ClientIP’ to store the IP address of the client. 

The trigger ‘tr_LogonAudit’ will fire for all logon events on the SQL Server. Whenever a user will successfully log in to the SQL Server, the trigger will capture the login name of the user with ‘ORIGINAL_LOGIN()’, the current timestamp using ‘GETDATE()’, and the client’s IP address using ‘HOSTNAME()’. It will be further inserted in the table ‘LogonAudit’.

Nested and Recursive Triggers

During an execution when a trigger fires another trigger, then it is known as a nested trigger. It could be a DML or DDL trigger. Recursive triggers are specific nested triggers where a trigger in the nested chain is fired twice. 

To use the nested trigger in a SQL Server instance, the configuration must be set as follows.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'nested triggers', 1;  
GO  
RECONFIGURE;  
GO 

To allow recursive triggers, the configuration must be set as follows.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'server trigger recursion', 1;  
GO  
RECONFIGURE;  
GO

To allow recursive triggers in a given database, the RECURSIVE_TRIGGERS option should be set on.

ALTER DATABASE SampleDB SET RECURSIVE_TRIGGERS ON;  
GO

Here is an example of a nested trigger.

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

-- Create the OrderAudit table to store audit records for orders
CREATE TABLE OrderAudit (
    AuditID INT PRIMARY KEY,
    OrderID INT,
    Action VARCHAR(50),
    AuditDateTime DATETIME
);

-- Create the AFTER INSERT trigger on the Orders table
CREATE TRIGGER tr_AfterInsertOrder
ON Orders
AFTER INSERT
AS
BEGIN
    -- Insert an audit record into the OrderAudit table
    INSERT INTO OrderAudit (OrderID, Action, AuditDateTime)
    SELECT OrderID, 'INSERT', GETDATE()
    FROM inserted;
END;
GO

-- Create the AFTER INSERT trigger on the OrderAudit table
CREATE TRIGGER tr_AfterInsertOrderAudit
ON OrderAudit
AFTER INSERT
AS
BEGIN
    -- Perform additional action for nested trigger example (e.g., print a message)
    PRINT 'New audit record inserted into OrderAudit table.';
END;
GO

In the above code, whenever a new order record is inserted into the ‘Orders’ table, the ‘AFTER INSERT’ trigger ‘tr_AfterInsertOrder’ is fired and an audit record is inserted into the ‘OrderAudit’ table. Then, the ‘tr_AfterInsertOrderAudit’ triggers is fired due to the insert in the ‘OrderAudit’ table and performs the additional action such as printing the message. 

Now, let us look at the example of the recursive trigger.

-- Create the EmployeeHierarchy table
CREATE TABLE EmployeeHierarchy (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT
);

-- Create the AFTER INSERT recursive trigger
CREATE TRIGGER tr_RecursiveEmployeeInsert
ON EmployeeHierarchy
AFTER INSERT
AS
BEGIN
    -- Update the ManagerID for newly inserted employees
    UPDATE e
    SET e.ManagerID = i.EmployeeID
    FROM EmployeeHierarchy e
    INNER JOIN inserted i ON e.EmployeeID = i.EmployeeID;

    -- Check for a recursive loop and stop after a certain depth (e.g., 5 levels)
    DECLARE @RecursionDepth INT;
    SET @RecursionDepth = (SELECT COUNT(*) FROM EmployeeHierarchy WHERE ManagerID = (SELECT EmployeeID FROM inserted));
    
    IF (@RecursionDepth <= 5)
    BEGIN
        -- Insert additional records to create a recursive loop for demonstration purposes
        INSERT INTO EmployeeHierarchy (EmployeeID, EmployeeName, ManagerID)
        SELECT i.EmployeeID + 100, 'Recursive Employee ' + CONVERT(NVARCHAR(10), i.EmployeeID + 100), i.EmployeeID
        FROM inserted i;
    END;
END;

In the above code, the ‘tr_RecursiveEmployeeInsert’ trigger fires whenever a new employee record is added to the ‘EmployeeHierarchy’ table. It updates the ‘ManagerID’ for the newly inserted employees to set it to their own ‘EmployeeID’. This creates a circular reference in the hierarchy. The trigger checks the recursion depth, i.e., how many levels of managers exist, and stops after a certain depth to prevent an infinite loop.

Conclusion

SQL Server offers three different types of triggers, data manipulation language triggers, data definition language triggers, and logon triggers. A user uses these different types of triggers in SQL depending upon their requirements. Additionally, one can also use DML and DDL triggers as nested and recursive triggers with proper configuration.