DML Trigger in SQL Server

Introduction

I am starting the article series on SQL Server Triggers. In the first article, we shall try to understand the overview, use cases, types, and DML category in detail. So, let’s begin.

Trigger in SQL Server Overview

As the name suggests, Triggers are DB objects invoked against DB operations like Insert, Update, and Delete on the record or column value. It is a special type of Stored Procedure that is automatically fired or executed when some event occurs in the database server. If you want to learn more about Triggers in SQL Server, Please explore our detailed article here- SQL Server Trigger.

In most cases, we create triggers on DML operations (Insert/Update/Delete); however, we can also create DDL triggers, such as Login triggers. Administrators mostly use DDL triggers.

Trigger Use cases

We use a trigger to perform certain actions based on data operation.

Let’s take a few examples to understand more.

• We need to notify the admins when records are deleted from some key tables.
• We must keep track of all the data changes made on any table for audit purposes.

Trigger Type

In SQL Server, we can create the following 3 types of triggers.

• DML Triggers (Data Manipulation Language Triggers)- Insert, Update, Delete
• DDL Triggers (Data Definition Language Triggers)-  Create, Alter, Drop
• Logon Triggers- Log on the trigger is fired when the user session is being established.

DML Triggers in SQL Server 

DML triggers are fired and executed when DML events occur in the table. DML events include INSERT, UPDATE, and DELETE statements.

Below is the syntax of the DML Trigger

CREATE TRIGGER [schema_name].[trigger_name]
ON { table | view}
[WITH dml_trigger_option]
{ FOR | AFTER | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
    {sql_statements}

DML Triggers can further be classified into the following types,

• Instead of Triggers, aka Before Trigger
  • Instead of Insert Trigger
  • Instead of Update Trigger
  • Instead of Delete Trigger
• After Triggers
  • After Insert Trigger
  • After Update Trigger
  • After Delete Trigger

Let’s begin the action on DML Triggers.

First, let’s create the table on which the trigger will be created.

CREATE TABLE StudentsReport (
StudentId INT NOT NULL PRIMARY KEY,
StudentName VARCHAR (50) NOT NULL,
Subject VARCHAR (50),
Marks INT NOT NULL
)

Now we shall insert some records.

INSERT INTO StudentsReport VALUES (1, 'Megha','english',90);
INSERT INTO StudentsReport VALUES (2, 'Ajay','english',98);
INSERT INTO StudentsReport VALUES (3, 'Mani','english',98);
INSERT INTO StudentsReport VALUES (4, 'Ram','english',100);
INSERT INTO StudentsReport VALUES (5, 'Aman','english',95);
GO

Let’s query the table now.

SELECT * FROM StudentsReport;

Instead of Insert Trigger

Let’s create Instead of Insert Trigger on the StudentsReport table.

CREATE TRIGGER TR_StudentsReport_Insert ON StudentsReport
INSTEAD OF INSERT
AS
BEGIN
                PRINT 'insert trigger is called and value is not inserted'
END

Instead of Insert trigger “TR_StudentsReport_Insert” created, let’s try inserting the value in the table.

INSERT INTO StudentsReport VALUES (6, 'Kala','english',92);

Instead of Insert Trigger executed and no value inserted in the table.

Instead of Update Trigger

Now Let’s create Instead of Update Trigger on the StudentsReport table.

CREATE TRIGGER TR_StudentsReport_Update ON StudentsReport
INSTEAD OF UPDATE
AS
BEGIN
                PRINT 'Update trigger is called and value is not updated'
END

Instead of Update Trigger “TR_StudentsReport_Update” created, let’s try updating the value in the table.

UPDATE StudentsReport SET Studentname = 'Usha' WHERE StudentId = 1;

Instead of Update Trigger executed and no value updated in that table.

Instead of Delete Trigger

Now Let’s create Instead of Delete Trigger on the StudentsReport table.

CREATE TRIGGER TR_StudentsReport_Delete ON StudentsReport
INSTEAD OF DELETE
AS
BEGIN
                PRINT 'Delete trigger is called and value is not deleted'
END

Instead of Delete Trigger “TR_StudentsReport_Delete” created, let’s try deleting the value from the table.

DELETE FROM StudentsReport WHERE StudentId = 1;

Instead of Delete Trigger executed and no value deleted from that table.

Let’s focus on DML - After Triggers

Now let’s create another table to understand the After Trigger Concept.

CREATE TABLE StudentsReportAudit
(
StudentId INT,
ModifiedDate DATETIME,
OperationType VARCHAR (50)
)
GO

After Insert Trigger

Now we shall create an After Insert Trigger on the StudentsReport table

CREATE TRIGGER TR_StudentsReport_AfterInsert ON StudentsReport
AFTER INSERT
AS
BEGIN
                INSERT INTO  StudentsReportAudit select StudentId, getdate(), 'Insert' from inserted
END

After Insert Trigger “TR_StudentsReport_AfterInsert” created. This trigger will be executed, and entry (StudentId, ModifiedDate, and OperationType) will be made on the StudentsReportAudit table whenever new data is inserted in the StudentsReport table.

Now try inserting the record on the table.

INSERT INTO StudentsReport VALUES (6, 'Kala','english',92);

SELECT * FROM StudentsReport;

SELECT * FROM StudentsReportAudit;

As you can see, the modified date/time and operation type of the StudentReportAudit table matches the StudentReport table.

After Update Trigger

Now we shall create an After Update Trigger on the StudentsReport table

CREATE TRIGGER TR_StudentsReport_AfterUpdate ON StudentsReport
AFTER UPDATE
AS
BEGIN
                INSERT INTO StudentsReportAudit select StudentId, getdate(), 'Update' from Updated
END

After Update Trigger “TR_StudentsReport_AfterUpdate” created. This trigger will be executed, and entry (StudentId, ModifiedDate, and OperationType) will be made on the StudentsReportAudit table whenever data is updated in the StudentsReport table.

Now try Updating the value on the table.

UPDATE StudentsReport SET Marks = 99 WHERE StudentId = 1;

SELECT * FROM StudentsReport;

SELECT * FROM StudentsReportAudit;

As you can see, the modified date/time and operation type of the StudentReportAudit table matches the StudentReport table.

After Delete Trigger

Now we shall create an After Delete Trigger on the StudentsReport table.

CREATE TRIGGER TR_StudentsReport_AfterDelete ON StudentsReport
AFTER DELETE
AS
BEGIN
                INSERT INTO  StudentsReportAudit select StudentId, getdate(), 'Delete' from Deleted
END

After Delete Trigger “TR_StudentsReport_AfterDelete” is created, this trigger will be executed, and entry (StudentId, ModifiedDate, and OperationType) will be made on the StudentsReportAudit table whenever data is deleted from the StudentsReport table.

Now try Deleting the value from the table.

DELETE FROM StudentsReport WHERE StudentId = 6;

SELECT * FROM StudentsReport;

SELECT * FROM StudentsReportAudit;

As you can see, the modified date/time and operation type of the StudentReportAudit table matches the StudentReport table.

Summary

Trigger is a very useful database object to keep track of data-related events in SQL Server. In this article, first, we have gone through the overview of triggers followed by use cases. We have also discussed the types of triggers and have gone through the instead of and after triggers in detail. We shall cover the remaining trigger types in subsequent articles.

I hope you liked the post. I look forward to your comments/suggestions.