Understanding DML Triggers in SQL Server

This article was published as a part of the Data Science Blogathon.

Introduction on SQL Server

SQL Server is an RDBMS developed and maintained by Microsoft. Triggers are used in SQL Server to respond to an event in the database server.

Trigger automatically gets fired when an event occurs in the database server. A trigger is a special type of stored procedure that runs in response to an event in the database server. Triggers in SQL Server are classified into three categories:

1. Logon Triggers

2. DDL Triggers

3. DML Triggers

When a user session is established with an instance of SQL Server, the LOGON event is raised. Logon triggers run in response to a LOGON event after the authentication phase of logging in gets successfully completed. Logon triggers do not get fired if authentication doesn’t succeed.

DDL events primarily run corresponding to the T-SQL statements which start with the keywords such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. Data Definition Language (DDL) triggers get executed in response to the DDL events.

Data Manipulation Language (DML) triggers get executed in response to the DML events.

In this article, we will study a category of triggers- DML triggers.

What are DML Triggers?

Data Manipulation Language (DML) triggers get executed when a DML event that affects the view or table defined in the trigger takes place in the database. In real-world scenarios, DML triggers can be used for querying other tables, enforcing data integrity and business rules, etc.

Syntax for creating DML Trigger:

CREATE/ALTER TRIGGER TriggerName
ON TableName/ViewName
[WITH TriggerAttributes]
Instead Of / AFTER/ FOR [Insert, Update, Delete]
AS
BEGIN
     TRIGGER Body
END

Here, ON TableName/ViewName is used to refer to the view or table name on which the trigger is defined. For/After option is used to specify that the trigger is executed only after the action of SQL statements. Instead Of is used to specify to the database engine to execute the trigger instead of executing the statement. Use atleast one of these three SQL statements or a combination of these statements is also accepted.

We will be using the Student table for demonstrating all the DML triggers in this article. Create a Student table using the below query:

CREATE TABLE STUDENT(std_id INT PRIMARY KEY IDENTITY(1,1),
std_name varchar(max) NOT NULL,
std_sub varchar(max) NOT NULL,
tot_marks decimal(4,2) NOT NULL DEFAULT 50
);

Example: For creating a For DML Trigger which should get executed after the INSERT DML SQL Server operation is performed on the Student table to roll back the insert statement use the below query:

CREATE TRIGGER trForInsertStudent
ON Student
FOR INSERT
AS
BEGIN
  PRINT 'YOU CANNOT PERFORM INSERT OPERATION ON STUDENT TABLE'
  ROLLBACK TRANSACTION
END

Now, if we try to insert the following record into the student table.

INSERT INTO STUDENT VALUES ('Rahul','MAths',79);

It gives us the below output.

Here, the INSERT statement is executed first, then trForInsertStudent trigger is fired and inside the trigger, a message is printed and the INSERT operation is rollbacked.

Types of DML Triggers

Now, we’ll study DML triggers in detail and learn about the different types of DML Triggers.

1. After trigger

AFTER trigger gets fired after the operation of the DELETE, INSERT, UPDATE, or MERGE DML statement is performed. In case any constraint violation occurs, then the AFTER trigger would never get executed. Thus, for any processing that might prevent constraint violations, this trigger cannot be used. For every INSERT, UPDATE, or DELETE SQL Statement provided in a MERGE statement, the corresponding trigger gets fired for each DML operation.

Syntax for creating After DML Trigger:

CREATE/ALTER TRIGGER TriggerName
ON TableName/ViewName
[WITH TriggerAttributes]
AFTER [Insert, Update, Delete]
AS
BEGIN
     TRIGGER Body
END

Here, ON TableName/ViewName is used to refer to the view or table name on which the trigger is defined. After option is used to specify that the trigger is executed only after the action of SQL statements. The INSERT, UPDATE, and DELETE specify on the execution of which SQL statement the DML trigger will get fired. Use atleast one of these three SQL statements or a combination of these statements is also accepted.

Example: For creating an After DML Trigger which should get executed after the Update DML SQL Server operation is performed on the Student table and update the student status in another table named STUDENTSTATUS use the below query:

CREATE TRIGGER trForUpdateStudent
ON STUDENT
FOR Update
AS
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='STUDENTSTATUS')
   CREATE TABLE STUDENTSTATUS(student_status varchar(250));
  INSERT INTO STUDENTSTATUS VALUES('Updated')
  PRINT 'YOU HAVE PERFORM UPDATE OPERATION ON STUDENT TABLE'
  PRINT 'STUDENT STATUS IS UPDATED IN THE STUDENTSTATUS TABLE '
END

Now, if we try to update one record present in the student table using the below query:

UPDATE STUDENT
SET tot_marks=95
WHERE std_name='Chaitanya'

It gives us the below output.

Here, the UPDATE statement is executed first, then trForUpdateStudent trigger is fired and inside the trigger, student status update message is inserted in STUDENTSTATUS table and messages are printed.

2. Instead Of Trigger

In the  Instead Of trigger, standard actions of the triggering statement are overridden. In real-world scenarios, the Instead Of trigger can be used to perform error handling in one or more table columns and perform additional actions before inserting, updating, or deleting the row or rows in the table or views.

Syntax for creating Instead Of DML Trigger:

CREATE/ALTER TRIGGER TriggerName
ON TableName/ViewName
[WITH TriggerAttributes]
Instead Of [Insert, Update, Delete]
AS
BEGIN
     TRIGGER Body
END

Here, TriggerName is the name of the trigger to be created or altered. ON TableName/ViewName is used to refer to the view or table name on which trigger is defined. Instead Of is used to specify to the database engine to execute the trigger instead of executing the statement. The INSERT, UPDATE, and DELETE specify on the execution of which SQL statement the DML trigger will get fired. Use atleast one of these three SQL statements or a combination of these statements is also accepted.

Example: For creating an Instead Of  DML Trigger which should get executed when either Insert, Update or Delete operation is performed on the Student table to roll back the applied SQL operation on Sunday use the below query:

CREATE TRIGGER trForAllDMLOperations
ON STUDENT
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
IF DATEPART(DW,GETDATE())= 1
  BEGIN
    PRINT 'DML OPERATIONS ARE RESTRICTED ON SUNDAY'
    ROLLBACK TRANSACTION
  END
ELSE
PRINT 'DML OPERATIONS IS PERFORMED'
END

Now, if we try to delete one record present in the student table using the below query on Wednesday:

DELETE STUDENT
WHERE std_name='Chaitanya'

It gives us the below output.

Conclusion on SQL Server

DML trigger is one of the most important triggers in SQL Server to enforce data integrity and business rules. To summarize, the following were the major takeaways about the DML trigger:

1. We have learned about what are the different types of triggers available in SQL Server.
2. We have also seen how we can prevent DML operations on a certain weekday using triggers.
3. We have also seen how to conditionally execute operations inside triggers.
4.  Apart from this, we have learned how to perform ROLLBACKS inside triggers, insert data into another table on trigger execution, etc.

 The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

chaitanya

Full Stack developer with 2.6 years of experience in developing applications using
Azure, SQL Server & Power BI. I love to read and write blogs. I am always willing to learn new technologies, easily adaptable to changes, and have a good time
management, problem-solving, logical and communication skills