DML Triggers in SQL Server

DML Triggers in SQL Server

In this article, I will discuss DML Triggers in SQL Server with some examples.

A trigger is a special type of stored procedure that executes automatically which will be used to provide restrictions on the tables and databases as well as enforce business rules and data integrity when language events executed.

The SQL Server provides four types of Triggers such as

  1. DML Triggers – Data Manipulation Language.
  2. DDL Triggers – Data Definition Language
  3. CLR triggers – Common Language Runtime
  4. Logon triggers

In this article, we will talk about the DML triggers in SQL Server.

What is DML Trigger in SQL Server?

As we know DML Stands for Data Manipulation language and it provides Insert, Update and Delete statement to perform the respective operation on the database tables or view which will modify the data of a table or view.

The DML triggers in SQL Server are executed automatically in response to DML events such as Insert, Update, and Delete. That means the DML Triggers are executed whenever the user tries to modify or change data of a table or view through the data manipulation language events such as INSERT, UPDATE, and DELETE statements.

The DML Triggers in SQL Server are used to enforce business rules and data integrity. That means the DML triggers are very much similar to constraints in the way they enforce integrity.

So, with the help of DML Triggers in SQL Server, we can enforce data integrity which cannot be done with the help of constraints that is comparing values with values of another table etc.

The syntax for creating a DML Triggers in SQL Server:

DML Triggers in SQL Server

While defining a trigger in SQL Server the ON TableName or ViewName in the syntax refers to the table or view on which we are defining the trigger.

The For/After option specifies that the trigger fires only after the SQL statements is executed whereas the InsteadOf option specifies that the trigger is executed on behalf of the triggering SQL statement.

The After Trigger cannot be defined on views.

From the above syntax, the INSERT, UPDATE, DELETE is used to specify which statement will activate the trigger and we need to use at least one option or combination of option can also be used.

NOTE: Insert, Update and Delete statements are also known as Triggering SQL statements because these three are responsible for the trigger to fire.

As per the specifications of triggers, we have two different types of DML trigger in SQL Server

  1. After Trigger (For Trigger)
  2. InsteadOf Trigger
What is After Trigger?

The After trigger in SQL Server fires after the triggering action. That means, the DML statements such as insert, update and delete statement causes an after trigger to fire after the respective statements complete its execution.

What is InsteadOf Trigger?

The InsteadOf trigger in SQL Server fires instead of the triggering action. That means, the DML statements such as the insert, update and delete statements causes an InsteadOf trigger to fire Instead of the respective statement execution.

We are going to use the below Employee table to understand the Triggers in SQL server.

DML Triggers in SQL Server

Please use below SQL Script to create and populate the Employee table with some test data.

-- Create Employee table
CREATE TABLE Employee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)
GO

-- Insert data into Employee table
INSERT INTO Employee VALUES (1,'Pranaya', 5000, 'Male', 3)
INSERT INTO Employee VALUES (2,'Priyanka', 5400, 'Female', 2)
INSERT INTO Employee VALUES (3,'Anurag', 6500, 'male', 1)
INSERT INTO Employee VALUES (4,'sambit', 4700, 'Male', 2)
INSERT INTO Employee VALUES (5,'Hina', 6600, 'Female', 3)

Example1: Create a Trigger which will restrict the INSERT operation on Employee table.

CREATE TRIGGER trInsertEmployee 
ON Employee
FOR INSERT
AS
BEGIN
  PRINT 'YOU CANNOT PERFORM INSERT OPERATION'
  ROLLBACK TRANSACTION
END

Let’s try to insert one record

INSERT INTO Employee VALUES (6,’Saroj’, 7600, ‘Male’, 1)

When we try to execute the above Insert statement it gives us the below error

DML Triggers in SQL Server

Example2: Create a Trigger which will restrict the UPDATE operation on the Employee table
CREATE TRIGGER trUpdateEmployee 
ON Employee
FOR UPDATE
AS
BEGIn
  PRINT 'YOU CANNOT PERFORM UPDATE OPERATION'
  ROLLBACK TRANSACTION
END

Let’s try to update one record in the Employee table

UPDATE Employee SET Salary = 90000 WHERE Id = 1

When we execute the above Update statement it will give us the below error

DML Triggers in SQL Server

Example3: Create a Trigger which will restrict the DELETE operation on the Employee table.

CREATE TRIGGER trDeleteEmployee 
ON Employee
FOR DELETE
AS
BEGIN
  PRINT 'YOU CANNOT PERFORM DELETE OPERATION'
  ROLLBACK TRANSACTION
END

Let’s try to delete one record from the Employee table

DELETE FROM Employee WHERE Id = 1

When we try to execute the above Delete statement, it gives us the below error

DML Triggers in SQL Server

Let’s understand where these triggers are gets created. Triggers are created within the Trigger folder which you can find when you expand the table as shown below.

DML Triggers in SQL Server

Example4: Create a table which will restrict all the DML operations on Employee table.

First, delete all the triggers that we already created on the Employee table. To delete you can the below syntax.

DROP Trigger TrggerName

Example:

DROP TRIGGER trDeleteEmployee
DROP TRIGGER trInsertEmployee
DROP TRIGGER trUpdateEmployee

Now let’s create a trigger which will restrict all the DML Operations on the Employee table.

CREATE TRIGGER trAllDMLOperationsOnEmployee 
ON Employee
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  PRINT 'YOU CANNOT PERFORM DML OPERATION'
  ROLLBACK TRANSACTION
END

Now, you cannot perform any DML operations on the Employee table because those operations are restricted by a trigger called trAllDMLOperationsOnEmployee.

Example5: Create a Trigger which will restrict all the DML operations on the Employee table on MONDAY only.
  1. SUN DAY = 1
  2. MON DAY = 2
  3. TUE DAY = 3
  4. WED DAY = 4
  5. THU DAY = 5
  6. FRI DAY = 6
  7. SAT DAY = 7
ALTER TRIGGER trAllDMLOperationsOnEmployee 
ON Employee
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  IF DATEPART(DW,GETDATE())= 2
  BEGIN
    PRINT 'DML OPERATIONS ARE RESTRICTED ON MONDAY'
    ROLLBACK TRANSACTION
  END
END

Example6: Create a Trigger which will restrict all the DML operations on the Employee table before 1 pm.

ALTER TRIGGER trAllDMLOperationsOnEmployee 
ON Employee
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  IF DATEPART(HH,GETDATE()) < 13
  BEGIN
    PRINT 'INVALID TIME'
    ROLLBACK TRANSACTION
  END	
END

In the next article, I will discuss SQL Server Magic Tables.

SUMMARY

In this article, I try to explain the DML Triggers in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *