Triggers in SQL Server

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

Triggers in SQL Server

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

  1. Instead of Triggers: The Instead Of trigger fires in SQL Server instead of the triggering action. That is instead of the DML statements such as Insert, Update, and Delete, this trigger is going to be fired.
  2. After Triggers: The After Triggers fires in SQL Server execute after the triggering action. That is 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.
  2. DDL Triggers – Data Definition Language
  3. CLR triggers – Common Language Runtime
  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.

Trigger Syntax in SQL Server

Let us understand the syntax in detail.

ON TableName or ViewName: It refers to the table or view name on which we are defining the trigger.

For/After/InsteadOf:  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.

Note: You cannot create After Trigger on views.

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

Examples to understand DML Trigger

Please use 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)
Example1: For Insert DML trigger

Create a Trigger that will restrict the INSERT operation on the 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 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

For Insert DML trigger

Example2: For Update DML Trigger

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 you try to execute the above Update statement it will give you the following error

For Update DML Trigger

Example3: For Delete DML Triggers

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

For Delete DML Triggers

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.

Where the Triggers are Created in SQL Server

Example4: For Insert/Update/Delete DML Trigger

Create a trigger that will restrict all the DML operations on the 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 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 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
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.

Leave a Reply

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