SQL Server Triggers

In previous post we talked about views, today we will review triggers and what purpose they serve. Triggers are useful but they should be used judiciously.

A trigger is a T-SQL script or a special stored procedure that is bound to the table and automatically executes based on a certain action on the table. For example, you can have a trigger that executes when a record is inserted, updated or deleted in a table.
There are two types of triggers - Data Definition Language (DDL) triggers and Data Manipulation Language (DML) triggers. DML triggers execute in response to data modification, for example, when you insert/update/delete a record. DDL triggers are executed when you modify data definition for example - adding a column to the table etc.

DML Triggers
DML Triggers can be further categorized into AFTER Trigger and INSTEAD OF Trigger. After triggers are executed after the statement with which they are associated with has been executed.
For example, suppose you have an audit table and anytime a record is inserted in the main table, you want to copy the newly inserted record in an audit table.

CREATE TRIGGER trgAfterInsert ON [dbo].[Customers] 
FOR INSERT
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =newRow.CustomerID from inserted newRow;    
    SELECT @FirstName = newRow.FirstName from inserted newRow;    
    SELECT @LastName = newRow.LastName from inserted newRow;    
    SELECT @EmailAddress = newRow.EmailAddress from inserted newRow;    
    
    INSERT INTO Customers_Audit
           (CustomerID,FirstName,LastName,EmailAddress) 
    values(@CustomerID,@FirstName,@LastName,@EmailAddress);

    PRINT 'AFTER INSERT trigger fired.'
GO

When you insert a record in customers table, it will fire this AFTER trigger and insert the newly inserted record into Customers_Audit table.

Similarly, you can create a trigger for UPDATE or DELETE.

Notice "from inserted" is a logical table inserted method that you can use to get the values from newly inserted rows. There is no logical update method, but you can use the same inserted method to get the updated rows and do something with them. For deleted rows, there is logical deleted method to get the deleted values. For example, I can create a new trigger when a record is deleted.

CREATE TRIGGER trgAfterDelete ON [dbo].[Customers] 
FOR DELETE
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =deletedRow.CustomerID from deleted deletedRow;    
    SELECT @FirstName = deletedRow.FirstName from deleted deletedRow;    
    SELECT @LastName = deletedRow.LastName from deleted deletedRow;    
    SELECT @EmailAddress = deletedRow.EmailAddress from deleted deletedRow;    
    
    INSERT INTO Customers_Audit
           (CustomerID,FirstName,LastName,EmailAddress) 
    values(@CustomerID,@FirstName,@LastName,@EmailAddress);

    PRINT 'AFTER DELETED trigger fired.'
GO

INSTEAD OF Trigger
INSTEAD OF Trigger allows you to intercept an execution and then roll back or commit transaction based on your criteria. For example, you don't want to delete certain records. You can create an INSTEAD OF trigger that can intercept the deletion, detect if the condition is satisfied and then either commit the deletion or rollback.

For example, I don't want to delete any customer whose last name is "Smith". I can create an INSTEAD OF trigger to prevent deleting such customers.

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Customers] 
INSTEAD OF DELETE
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =deletedRow.CustomerID from deleted deletedRow;    
    SELECT @FirstName = deletedRow.FirstName from deleted deletedRow;    
    SELECT @LastName = deletedRow.LastName from deleted deletedRow;    
    SELECT @EmailAddress = deletedRow.EmailAddress from deleted deletedRow;    
    
    IF (@LastName='Smith')
        BEGIN
            RAISERROR('Cannot Delete',16,1)
            ROLLBACK;
        END
    ELSE
        BEGIN
            DELETE FROM Customers WHERE CustomerID=@CustomerID
            COMMIT
        END
        
GO

DDL Trigger
DDL Triggers are fired when a table schema is modified. If I want to prevent any table modification, I can create a trigger like this...

CREATE TRIGGER TrgAlterTable
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
PRINT 'You must disable Trigger "TrgAlterTable" to drop or alter tables!' 
ROLLBACK ;

Unless I disable or drop this trigger, I will not be able to drop or modify a table.

Enabling / Disabling a Trigger
You can disable all triggers or a specific trigger by executing the following script.

ALTER TABLE Customers {ENABLE|DISBALE} TRIGGER ALL 

To enable or disable a specific trigger, simply replace "ALL" with the trigger name.

While triggers serve a useful purpose, one of the issue I have with them is that they are hidden and after you have long forgotten, troubleshooting an issue becomes a problem because you just don't think about the triggers. That's why it is important to have some kind of messaging/logging in triggers to trace an issue.

Thank you.