SQL Server Trigger Example

Problem

You’ve already learned about SQL queries and stored procedures, but now you want to learn about SQL Server triggers. This tip will serve as a starting point and a guide to creating SQL Server triggers. This article will cover several SQL Server trigger examples.

Solution

Triggers are one of the most misunderstood topics for people new to SQL Server. Maybe this is due to the fact that they allow almost all the same functionality as stored procedures, making the inexperienced developer confused about whether to create a stored procedure or trigger.

What is a SQL Server Trigger?

A SQL Server trigger is a piece of procedural code. It is just like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. To name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance.

There are three main characteristics that make triggers different than stored procedures:

• Triggers cannot be manually executed by the user.
• There is no chance for triggers to receive parameters.
• You cannot commit or rollback a transaction inside a trigger.

The fact that it’s impossible to use parameters on triggers is not a limitation to receive information from the firing event. As you will see further on, there are alternatives to obtain information about the firing event.

Classes of SQL Server Triggers

There are two classes of triggers in SQL Server:

• DDL (Data Definition Language) triggers. This class of triggers fires upon events that change the structure (like creating, modifying or dropping a table), or in certain server related events like security changes or statistics update events.
• DML (Data Modification Language) triggers. This is the most used class of triggers. In this case the firing event is a data modification statement; it could be an insert, update or delete statement either on a table or a view.

Additionally, DML triggers have different types:

• FOR or AFTER [INSERT, UPDATE, DELETE]: These types of triggers are executed after the firing statement ends (either an insert, update or delete).
• INSTEAD OF [INSERT, UPDATE, DELETE]: Contrary to the FOR (AFTER) type, the INSTEAD OF triggers executes instead of the firing statement. In other words, this type of trigger replaces the firing statement. This is very useful in cases where you need to have cross database referential integrity.

What is The Importance of SQL Server Triggers?

One of the fundamental characteristics of relational databases is data consistency. This means that the information stored in the database must be consistent at all times for every session and every transaction. The way relational database engines like SQL Server implement this is by enforcing constraints like primary keys and foreign keys. But sometimes that is not enough.

In SQL Server there is no chance to enforce referential integrity between two tables using foreign keys if those tables are in different databases or different servers. In such case the only way you can implement it is by using triggers.

How Do I Know Which Rows Were Updated, Inserted or Deleted using a SQL Server DML Trigger?

In the case of DML triggers, there are two virtual tables during the execution of the trigger that holds the data being affected by the trigger execution. Those tables are named inserted and deleted and they have the same table structure as their base table.

Something to keep in mind is that the inserted and deleted tables are not always available together (i.e. you can have the inserted table, but not the deleted table or vice versa). You will find more information about these tables in the following tip Understanding SQL Server inserted and deleted tables for DML triggers.

SQL Server DML Trigger Syntax

In the next code section, you will see the basic CREATE TRIGGER syntax.

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

Additionally, the next table describes each of the arguments of the CREATE TRIGGER syntax.

SQL Server Trigger Usage Scenarios

There are two clear scenarios when triggers are the best choice: auditing and enforcing business rules. By using a trigger, you can keep track of the changes on a given table by writing a log record with information about who made the change and what was changed in the table.

Maybe you think that you can do the same in the application with a stored procedure that handles data modification like inserts and updates. You can use a stored procedure, but in such a case you will not be able to log the changes that were made directly to the database from outside the application.

The same happens when you want to enforce business rules with a stored procedure. If someone modifies the data on the base table from outside the application you can have a problem because the data consistency cannot be guaranteed. To avoid this issue, you would make sure the stored procedure was the only way to access the table.

Sample SQL Server DML Trigger

Let’s suppose that we have a database for the human resource department. This database contains a table Employees to keep personnel information and salaries. With the help of a trigger we can keep an audit record in a separate table that contains each record modification as well as the user who made the change and the time it happened.

Create Sample Tables

First, we must create the Employees table.

CREATE TABLE Employees
    (
      EmployeeID integer NOT NULL IDENTITY(1, 1) ,
      EmployeeName VARCHAR(50) ,
      EmployeeAddress VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2)
      PRIMARY KEY CLUSTERED (EmployeeID)
    )
GO

Then we have to create the EmployeesAudit table to store the audit records. This table has the same structure as the Employees table, but includes an AuditId column as a primary key, ModifiedDate to keep the date of modification, ModifiedBy so we can know who modified the Employees table and finally Operation where we will indicate the DML operation that generated the audit record with one of three letters (I for insert, U for Update and D for Delete).

CREATE TABLE EmployeesAudit
    (
      AuditID INTEGER NOT NULL IDENTITY(1, 1) ,
      EmployeeID INTEGER ,
      EmployeeName VARCHAR(50) ,
      EmployeeAddress VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2) ,
      ModifiedBy VARCHAR(128) ,
      ModifiedDate DATETIME ,
      Operation CHAR(1) 
      PRIMARY KEY CLUSTERED ( AuditID )
    )
GO

In order to be able to test the sample trigger we need to add some data to the Employees table.

INSERT INTO dbo.Employees
        ( EmployeeName ,
          EmployeeAddress ,
          MonthSalary
        )
SELECT 'Mark Smith', 'Ocean Dr 1234', 10000
UNION ALL
SELECT 'Joe Wright', 'Evergreen 1234', 10000
UNION ALL
SELECT 'John Doe', 'International Dr 1234', 10000
UNION ALL
SELECT 'Peter Rodriguez', '74 Street 1234', 10000
GO

Create SQL Server trigger example

Now that we have the test environment set, it’s time to create our trigger. Take a look at the SQL Server trigger example.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees
    FOR INSERT, UPDATE, DELETE
AS
    DECLARE @login_name VARCHAR(128)
 
    SELECT  @login_name = login_name
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@SPID
 
    IF EXISTS ( SELECT 0 FROM Deleted )
        BEGIN
            IF EXISTS ( SELECT 0 FROM Inserted )
                BEGIN
                    INSERT  INTO dbo.EmployeesAudit
                            ( EmployeeID ,
                              EmployeeName ,
                              EmployeeAddress ,
                              MonthSalary ,
                              ModifiedBy ,
                              ModifiedDate ,
                              Operation
                            )
                            SELECT  D.EmployeeID ,
                                    D.EmployeeName ,
                                    D.EmployeeAddress ,
                                    D.MonthSalary ,
                                    @login_name ,
                                    GETDATE() ,
                                    'U'
                            FROM    Deleted D
                END
            ELSE
                BEGIN
                    INSERT  INTO dbo.EmployeesAudit
                            ( EmployeeID ,
                              EmployeeName ,
                              EmployeeAddress ,
                              MonthSalary ,
                              ModifiedBy ,
                              ModifiedDate ,
                              Operation
                            )
                            SELECT  D.EmployeeID ,
                                    D.EmployeeName ,
                                    D.EmployeeAddress ,
                                    D.MonthSalary ,
                                    @login_name ,
                                    GETDATE() ,
                                    'D'
                            FROM    Deleted D
                END  
        END
    ELSE
        BEGIN
            INSERT  INTO dbo.EmployeesAudit
                    ( EmployeeID ,
                      EmployeeName ,
                      EmployeeAddress ,
                      MonthSalary ,
                      ModifiedBy ,
                      ModifiedDate ,
                      Operation
                    )
                    SELECT  I.EmployeeID ,
                            I.EmployeeName ,
                            I.EmployeeAddress ,
                            I.MonthSalary ,
                            @login_name ,
                            GETDATE() ,
                            'I'
                    FROM    Inserted I
        END
GO

Basically the code consists of obtaining the user who is modifying the Employees table by looking at the sys.dm_exec_sessions Dynamic Management View for the session with the current SPID. After that the trigger inserts one record in the EmployeesAudit table for each record inserted, updated or deleted in the Employees table as well as the current time and the DML operation that fired the trigger.

Test SQL Server Trigger

In order to test the SQL Server trigger example, I created three queries. I put the code inside a transaction just to keep order in my test environment, you can omit this.

The first of those queries is an update.

BEGIN TRANSACTION
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
 
UPDATE  Employees
SET     EmployeeName = 'zzz'
WHERE   EmployeeID = 1
 
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
 
SELECT  *
FROM    dbo.EmployeesAudit
ROLLBACK TRANSACTION

On the next screen capture you will see the updated record in the Employees table and the new record in EmployeesAudit that keeps track of the DML operation over the Employees table.

The second query is an insert of two rows to the Employees table.

BEGIN TRANSACTION
INSERT  INTO dbo.Employees
        ( EmployeeName ,
          EmployeeAddress ,
          MonthSalary
        )
        SELECT  'zz' ,
                'dsda' ,
                10000
        UNION ALL
        SELECT  'Markus Rubius' ,
                'dsda' ,
                6000
SELECT  *
FROM    dbo.Employees
SELECT  *
FROM    dbo.EmployeesAudit
ROLLBACK TRANSACTION

On the next screen capture you will see the two inserted rows in the Employees table and their respective audit record in the EmployeesAudit table.

Finally, the third query is a delete statement on the Employees table.

BEGIN TRANSACTION
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
DELETE  FROM dbo.Employees
WHERE   EmployeeID = 1
 
SELECT  *
FROM    dbo.EmployeesAudit
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
ROLLBACK TRANSACTION

On the next screen capture you will see the row deleted from the Employees table and its respective audit record in the EmployeesAudit table.

Next Steps

• If you found it hard to understand the way I captured which DML operation was performed in the Employees table take a look at this tip: Understanding SQL Server inserted and deleted tables for DML triggers.
• For an example of an INSTEAD OF trigger you can check out this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
• You can find more information about the sys.dm_exec_sessions DMV here: Understanding and Using sys.dm_exec_sessions in SQL Server.
• For a more detailed explanation of the EXECUTE AS command take a look at the next tip: Granting permission with the EXECUTE AS command in SQL Server.
• Stay tuned to the SQL Server Triggers Tips Category for more tips and tricks.
• The concepts discussed will work with SQL Server 2005 through SQL Server 2022 and future versions.
• CREATE TRIGGER (Transact-SQL)