What is DML Trigger?

Data Manipulation Language (DML) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DML operation like INSERT, UPDATE OR DELETE is fired on a Table or View.

To understand DML triggers with extensive list of examples let us create a Demo database and a Table by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsDMLTriggerDemo
GO
USE SqlHintsDMLTriggerDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO

[ALSO READ]Data Definition Language (DDL) Triggers in Sql Server

Types of DML Triggers

Following are the two types of DML Triggers

1. AFTER Triggers

AFTER Triggers are executed after the DML statement completes but before it is committed to the database. AFTER Triggers if required can rollback it’s actions and source DML statement which invoked it.

Example 1: Create an AFTER Trigger AfterTriggerExample1 for the DML INSERT, UPDATE and DELETE OPERATION on the Customer table by executing the following script

CREATE TRIGGER AfterTriggerExample1
ON Customer
FOR INSERT, UPDATE, DELETE
AS 
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample1 executed!'
END

Now try inserting a record into the Customer table by executing the following query

INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Basavaraj','Biradar')

RESULT:

From the above result we can see that the INSERT statement has resulted in the automatic execution of the AFTER trigger AfterTriggerExample1

Let us verify by executing the following query, whether the above INSERT statement has inserted a record into the Customer table

SELECT * FROM dbo.Customer

RESULT:

From the above results we can see that on a Table with a AFTER trigger, the trigger will be executed once the triggering DML action completes and before it is committed

Note in the above example just to make the understanding simple, in the Trigger just written a PRINT statement. In real world we don’t write triggers to return some message or results. They are useful for various aspects like: i) Auditing the data changes (i.e. log the changes to the table data in an audit table) ii) Enforce business rules and data integrity etc.

A table can multiple AFTER triggers and for the same trigger action too we can have multiple triggers.

[ALSO READ] Logon Triggers in Sql Server
Example 2: Multiple AFTER Triggers

Let us execute the following script to create a one more trigger on the Customer table and this time for the triggering action INSERT only

CREATE TRIGGER AfterTriggerExample2
ON Customer
FOR INSERT
AS 
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample2 executed!'
END

Now try inserting a record into the Customer table by executing the following query

INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Kalpana','Patil')

RESULT:

From the above result we can see that the AFTER trigger which is created in Example 1 (i.e. AfterTriggerExample1) and another AFTER trigger which we created in this example (i.e. AfterTriggerExample2) are executed when we try to insert a record in the Customer table. As both these triggers are configured to execute for the trigger action INSERT on the Customer table.

Let us verify by executing the following query, whether the above INSERT statement has inserted a record into the Customer table

SELECT * FROM dbo.Customer

RESULT:

If a table has multiple AFTER triggers, then we can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are executed in an undefined order which can’t be controlled.

Drop the two AFTER triggers created in the above examples by executing the following script

DROP TRIGGER AfterTriggerExample1
DROP TRIGGER AfterTriggerExample2

INSERTED and DELETED Logical Tables

DML Triggers have access to the two special logical Tables named INSERTED and DELETED, these are the temporary tables whcih Sql Server manages. These tables structure will be same as that of the table on which the DML action is fired and holds the old and new values of the rows which are modified by the DML statement.

DELETED logical table will hold the rows which are delted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE or UPDATE operation. An UPDATE DML operation is internally executed as first the deletion of the existing record and then insertion of the new record.

INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.

Below table depicts the contents of the INSERTED and DELETED logical tables for three DML operations

[ALSO READ] INSERTED and DELETED logical tables with extensive list of examples

2. INSTEAD OF Triggers

INSTEAD OF Triggers are the triggers which gets executed automatically in place of triggering DML (i.e. INSERT, UPDATE and DELETE) action. It means if we are inserting a record and we have a INSTEAD OF trigger for INSERT then instead of INSERT whatever action is defined in the trigger that gets executed.

Example 1: Create an INSTEAD OF Trigger on the Customer Table for the INSERT DML operation by executing the following script

 
CREATE TRIGGER INSTEADOFTriggerExample 
ON Customer
INSTEAD OF INSERT
AS
BEGIN
	PRINT('Instead of trigger is Executed!')
END 

Execute the following INSERT statement to check the behavior of the INSTEAD OF trigger

 
INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Monty','Biradar') 

RESULT:

Let us verify whether the above INSERT statement has inserted a record in the Customer table by executing the following SELECT statement

 
SELECT * FROM dbo.Customer  

RESULT:

From the result we can see that INSERT statement triggered the execution of the INSTEAD OF trigger instead of inserting the record into the Customer table.

Example 2: Try to add one more INSTEAD of Trigger on the Customer table for the INSERT DML operation by executing the following script

CREATE TRIGGER AnotherINSTEADOFTrigger 
ON Customer
INSTEAD OF INSERT
AS
BEGIN
    PRINT 'Instead of trigger is Executed!'
END

RESULT:

Msg 2111, Level 16, State 1, Procedure AnotherINSTEADOFTrigger, Line 1
Cannot create trigger ‘AnotherINSTEADOFTrigger’ on table ‘Customer’ because an INSTEAD OF INSERT trigger already exists on this object.

From the result we can see that Sql Server doesn’t allow multiple INSTEAD OF triggers on one table for the same DML triggering action

Example 3: This example demonstrates that a Table can have one INSTEAD of Trigger for each Triggering DML actions INSERT, UPDATE and DELETE

Let us execute the following statement to add an INSTEAD OF Trigger on the Customer table for the UPDATE DML triggering action

CREATE TRIGGER OneMoreINSTEADOFTrigger 
ON Customer
INSTEAD OF UPDATE
AS
BEGIN
    PRINT 'Instead of trigger is Executed!'
END

RESULT:

From the result we can confirm that: At max we can have one INSTEAD OF Trigger on a Table for each DML triggering actions

[ALSO READ]
Introduction to Triggers
Data Definition Language (DDL) Triggers in Sql Server
INSERTED and DELETED Logical Tables
Logon Triggers