Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Trigger Example


By:   |   Last Updated: 2019-02-26   |   Comments (1)   |   Related Tips: More > Triggers

Problem

You've already learned how to write 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.

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, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just 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 <Options> ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }			

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

Argument Description
WITH <Options> In this argument you can specify additional options for the creation of the trigger. I will cover this further on.
FOR | AFTER | INSTEAD OF Indicates when the trigger must fire when a given event happens, like an insert, update or delete event.
[INSERT], [UPDATE] , [DELETE] The DML event (or list of events) that will cause the trigger to fire.


WITH Option Description Remarks
ENCRYPTION Encrypts the code of the Trigger. Doesn't work with Memory Optimized Tables
EXECUTE AS Changes the security context on which the trigger will execute Required for triggers on memory-optimized tables.
NATIVE_COMPILATION Compiles the trigger code into a binary to make it run natively. Required for triggers on memory-optimized tables.
SCHEMABINDING Ensures that tables that are referenced by a trigger cannot be dropped or altered. Required for triggers on memory-optimized tables.

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.

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			

Now that we have the test environment set, it's time to create our trigger. Take a look at the code below.

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.

In order to test the trigger, 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.

This is how the sample trigger behaves when its fired by an update statement.

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.

This image shows that our trigger created one record for each row affected on the base 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.

This is how the sample trigger behaves when its fired by a delete statement.
Next Steps


Last Updated: 2019-02-26


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, March 02, 2019 - 6:26:11 AM - Carlos Malaca Back To Top

 Dear Farina,
Very detail explanation.

Caro Farinha. Uma Boa introdução aos triggers. Parabéns. Bons links para mais informações.

Abraço

Carlos Malaca


Learn more about SQL Server tools