SQL | Triggers in SQL Server – Definition, Types, Syntax and Examples

Last modified date:

A trigger is a database object (procedure) that works as a watchdog for certain event. Using database triggers we can catch this event and initiate some additional action – such as logging or rejecting the action.

The event that initiates the action may be:

  • INSERT or UPDATE records in the table
  • DELETE records from the table
  • CREATE / DROP an object in the database
  • User Login
  • Many Others

What are triggers used for?

Triggers are used mainly for auditing objects in the database (but not always)

For example, if you want to keep track of what is changing in a certain table, then by creating a trigger, you can, for example, log these changes or send an email notification with information that the change has occurred.

Before creating a trigger, some aspects should be considered – especially performance is a very important factor. If we create a trigger, of course, all operations takes longer. For large tables, it could be a huge performance problem.

We can classify triggers into 3 categories according to the event we want to monitor.

  • DML (Data manipulation language) triggers
  • DDL (Data definition language) triggers
  • Logon triggers

DML (Data Manipulation Language) Trigger – INSERT, UPDATE, DELETE

DML commands change records in a table or view:

  • Insert records using INSERT
  • Editing records using UPDATE or
  • Delete them using DELETE

DML Trigger is activated when such an operation is performed over the table.

Syntax CREATE DML Triggers (general)

CREATE TRIGGER [schema].[trigger_name]
ON {table | view}
{ FOR | AFTER | INSTEAD OF }
{ INSERT [,] UPDATE [,] DELETE }
AS {sql_statement}

The syntax is based on Microsoft documentation and I have simplified it slightly to make it easier to read. When creating a trigger, we must define the DML type (INSERT, UPDATE, DELETE). We can fill 1 or all 3 separated by commas.

We also define what the trigger should do (FOR/AFTER and INSTEAD OF). Therefore, DML triggers can be classified into 2 categories (see below)

Logical tables – [inserted] and [deleted] for DML Triggers

When activating the DML trigger, two special logic tables are created (background) – [inserted] and [deleted]. That’s a very important and useful part of entire trigger.

These logical tables contain data from source table that were affected by the DML operation (INSERT, UPDATE, or DELETE).

  • [inserted] table contains new and changed records
  • [deleted] table contains deleted records

INSTEAD OF Triggers

For INSTEAD OF trigger, the DML operation is not performed at all and <sql_statement> is executed instead. In other words, we use a trigger to restrict the DML operation (Insert, Update or Delete) from being executed, and instead perform another operation.

Info: A typical example of use may be a test for duplicity. For example we want to insert only records that are not present in a destination table. Therefore, we do not want to restrict the whole DML operation, but reject only certain duplicate records.

Example:

Let’s have a [Test] table that contains a column [ID] and [Text_]. We want to insert unique values into this table based on the [ID] column. Any attempts to insert a duplicate record must be ignored

Příklad na instead of trigger

Solution:

CREATE TRIGGER [dbo].[Remove_Duplicities]
ON [Temp].[dbo].[Test]
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO [Temp].[dbo].[Test] (
    [ID]
    ,[Text_]
  )
  SELECT
    [ID]
    ,[Text_]
  FROM [inserted]
  WHERE [ID] NOT IN (SELECT DISTINCT [ID] FROM [Temp].[dbo].[Test])
END

  GO

–We try to insert three records of which 1 is a duplicate record
INSERT INTO [Temp].[dbo].[Test] (
  [ID]
  ,[Text_]
)
VALUES (2, 'Hi'),
(3, 'Hello'),
(1, 'Hey')

Instead of trigger vysledek

Duplicate record with ID=1 (Hey) has been ignored. Also note that we work with the [inserted] logical table in the trigger definition.

FOR/AFTER Triggers

FOR / AFTER, <sql_statement> is executed after the given DML operation. It means that the operation takes place and then the trigger is executed (for example, logging).

Info: We are able to “cancel” the entire transaction in a trigger using ROLLBACK unless any of our conditions are met.

Example: Let’s have a table as in the previous example. Using the AFTER trigger, we would like to log operations such as INSERT and DELETE. We want to log the DateTime, type of operation, ID from the source table and the user who performed operation.

CREATE TABLE [dbo].[DML_Audit] (
  [ID] INT IDENTITY(1,1)
  ,[DML_OPERATION] VARCHAR(255)
  ,[DML_DATETIME] DATETIME
  ,[DML_USER] VARCHAR(255)
  ,[ID_SourceTable] INT
)

GO

CREATE TRIGGER [dbo].[tr_DML_Audit]
ON [Temp].[dbo].[Test]
AFTER INSERT,DELETE
AS
BEGIN
INSERT INTO [dbo].[DML_Audit] (
  [DML_OPERATION]
  ,[DML_DATETIME]
  ,[DML_USER]
  ,[ID_SourceTable]
)
SELECT
  'INSERT' AS [DML_OPERATION]
  ,GETUTCDATE() AS [DML_DATETIME]
  ,SUSER_NAME() AS [DML_USER]
  ,[inserted].[ID] AS [ID_SourceTable]
FROM [inserted]

UNION ALL

SELECT
  'DELETED' AS [DML_OPERATION]
  ,GETUTCDATE() AS [DML_DATETIME]
  ,SUSER_NAME() AS [DML_USER]
  ,[deleted].[ID] AS [ID_SourceTable]
FROM [deleted]
END

DELETE FROM [Temp].[dbo].[Test] WHERE ID = 2

Delete – DROP DML Trigger

DROP TRIGGER [schema].[trigger_name]

DDL (Data Definition Language) Trigger

DDL Commands are operations with objects as such. While DML triggers are scoped to objects, DDL trigger has a database or server scope. DDL trigger is activated if any object in the database is affected:

  • Create
  • Alter
  • Drop
  • Grant
  • Deny
  • Revoke
  • Update statistics

Info: DDL Trigger never creates logical tables [inserted], [deleted] like DML triggers

Syntax – CREATE DDL Triggers

CREATE TRIGGER [name]
ON { DATABASE | ALL SERVER}
[WITH option]
FOR {event_type}
AS {sql_statement}

If you want to use a DDL operation for an event_type (group of objects) in a trigger, use an underscore and then an object. E.g

  • CREATE_TABLE
  • DROP_VIEW
  • Etc.

Example: In the [Temp] database, we want to disable ALTER View operations in bulk

USE [Temp]
GO
CREATE TRIGGER [restrict_alter_view]
ON DATABASE
FOR ALTER_VIEW
AS
BEGIN
PRINT 'Alter view operation is restricted'
ROLLBACK TRANSACTION
END

If we try to perform ALTER view operation after creating the trigger, we’ll get the answer:

Alter view operation is restricted
Msg 3609, Level 16, State 2, Procedure XYZ, Line 3 [Batch Start Line 9]
The transaction ended in the trigger. The batch has been aborted.

Delete – DROP DDL trigger

DROP TRIGGER [trigger_name] ON DATABASE;

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Don't Miss Out SQL Administration SQL Commands

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.