Triggers in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Triggers in SQL Server with Examples

In this article, I am going to discuss Triggers in SQL Server with Examples and in which scenarios we need to use triggers. While we are discussing triggers we will also discuss two important tables i.e. inserted and deleted. As part of this article, we are going to discuss the following pointers in detail.

1. What are Triggers in SQL Server?
2. Types of Triggers in SQL Server
3. What are DML Triggers in SQL Server?
4. Examples to understand DML Trigger
5. Where the Triggers are Created in SQL Server?
6. Why do we need DML Triggers in SQL Server?
7. Multiple examples to understand the above concepts

What are Triggers in SQL Server?

Triggers are nothing but they are logic’s like stored procedures that can be executed automatically before the Insert, Update or Delete happens in a table or after the Insert, Update, or Delete happens in a table. In simple words, we can say that, if you want to execute some pre-processing or post-processing logic before or after the Insert, Update, or Delete in a table then you need to use Triggers in SQL Server.

There are two types of triggers. They are as follows:

1. Instead of Triggers: The Instead Of triggers are going to be executed instead of the corresponding DML operations. That means instead of the DML operations such as Insert, Update, and Delete, the Instead Of triggers are going to be executed.
2. After Triggers: The After Triggers fires in SQL Server execute after the triggering action. That means once the DML statement (such as Insert, Update, and Delete) completes its execution, this trigger is going to be fired.

Types of Triggers in SQL Server:

There are four types of triggers available in SQL Server. They are as follows:

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

In this article, we are going to discuss the DML triggers and the rest are going to discuss in our upcoming articles.

What are DML Triggers in SQL Server?

As we know DML Stands for Data Manipulation Language and it provides Insert, Update and Delete statements to perform the respective operation on the database tables or view which will modify the data. The triggers which are executed automatically in response to DML events (such as Insert, Update, and Delete statements) are called DML Triggers.

The syntax for creating a DML Triggers in SQL Server:

You can create a DML Trigger in SQL Server by using the following syntax.

Let us understand the syntax in detail.

1. ON TableName or ViewName: It refers to the table or view name on which we are defining the trigger.
2. For/After/InsteadOf:  The For/After option specifies that the trigger fires only after the SQL statements are executed whereas the InsteadOf option specifies that the trigger is executed on behalf of the triggering SQL statement. You cannot create After Trigger on views.
3. INSERT, UPDATE, DELETE: The INSERT, UPDATE, DELETE specify which SQL statement will fire this trigger and we need to use at least one option or the combination of multiple options is also accepted.

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

Examples to understand DML Trigger in SQL Server:

In this article, we are going to discuss some simple examples to understand the triggers and from our next article, we will see the real-time usages of Triggers.  Here, we are going to use the following Employee table to understand the Triggers.

Please use the below SQL Script to create and populate the Employee table.

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

Example: For/After Insert DML Trigger in SQL Server

So, basically what we want is, we want to create a For/After DML Trigger which should fire after the INSERT DML operation when performing on the Employee table. The trigger should restrict the INSERT operation on the Employee table. To do so, please execute the below query. As you can see in the below query, this trigger is created for the Employee table. We also specify that is a FOR trigger for the INSERT DML operation and as part of the trigger body, we are simply rollbacking the transaction which will roll back the insert statement.

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

Let’s try to insert the following record into the employee table.
INSERT INTO Employee VALUES (6, ‘Saroj’, 7600, ‘Male’, 1)
When you try to execute the above Insert statement it gives you the below error. First, the INSERT statement is executed, and then immediately this trigger fired and roll back the INSERT operation as well as print the message.

Example: For/After Update DML Trigger in SQL Server

So, basically what we want is, we want to create a For/After DML Trigger which should fire after the UPDATE DML operation when performing on the Employee table. The trigger should restrict the UPDATE operation on the Employee table. To do so, please execute the below query. As you can see in the below query, this trigger is created for the Employee table. We also specify that is a FOR trigger for the UPDATE DML operation and as part of the trigger body, we are simply rollbacking the transaction which will roll back the UPDATE statement and print a message.

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 you try to execute the above Update statement it will give you the following error. First, the UPDATE statement is executed, and then immediately this trigger fired and roll back the UPDATE operation as well as print the message.

Example3: For/After Delete DML Triggers in SQL Server

So, basically what we want is, we want to create a For/After DML Trigger which should fire after the DELETE DML operation when performing on the Employee table. The trigger should restrict the DELETE operation on the Employee table. To do so, please execute the below query. As you can see in the below query, this trigger is created for the Employee table. We also specify that is a FOR trigger for the DELETE DML operation and as part of the trigger body, we are simply rollbacking the transaction which will roll back the DELETE operation and print a message.

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. First, the DELETE statement is executed, and then immediately this trigger fired and roll back the DELETE operation as well as print the message.

Where the Triggers are Created in SQL Server?

In SQL Server, the Triggers are created within the Trigger folder which you can find when you expand the table as shown in the below image.

Example4: For Insert/Update/Delete DML Trigger in SQL Server

So, basically what we want is, we want to create a For/After DML Trigger which should fire after any DML operation (INSERT, UPDATE, and DELETE) when performing on the Employee table. The trigger should restrict all the DML operations on the Employee table. As you can see in the below query, this trigger is created for the Employee table. We also specify that is a FOR trigger for the INSERT, UPDATE, DELETE DML operation and as part of the trigger body, we are simply rollbacking the transaction which will roll back all DML operation and print a message.

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 execute the below query to create a trigger that 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 that 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 that 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

Why do we need DML Triggers in SQL Server?

DML Triggers are used to enforce business rules and data integrity. These triggers are very much similar to constraints in the way they enforce integrity. So, with the help of DML Triggers, we can enforce data integrity which cannot be done with the help of constraints that is comparing values with values of another table, etc.

In the next article, I am going to discuss two important tables i.e. Inserted and Deleted Tables in SQL Server which you can only access within a trigger. Here, in this article, I try to explain the basics of Triggers in SQL Server with Examples. I hope you enjoy this Triggers in SQL Server with Examples article.