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.

Trigger – event that initiates the action

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.

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

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 in SQL Server

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.

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

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')

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;