Trigger In SQL Server

In this article, We will learn about the Trigger concept in SQL Server.

• A trigger is a special type of stored procedure that fires automatically when an event occurs in the database server.
• There are different types of events that can activate a trigger like inserting, deleting, or updating rows in a table, a user logging into a database server instance, an update to a table column, creating a table, altering a table, dropping a table, etc.
• A trigger is called a special procedure because it cannot be called directly like a stored procedure. The key distinction between the trigger and procedure is that a trigger is fired automatically when a data modification event occurs against a table. on the other hand, A stored procedure must be invoked directly.

There are three main characteristics that make triggers different than stored procedures:

1. Triggers cannot be manually executed by the user.
2. There is no chance for triggers to receive parameters.
3. You cannot commit or rollback a transaction inside a trigger.

When do we use triggers?

• Triggers will be helpful when we need to execute some events automatically on certain desirable scenarios.
• For example, we have a constantly changing table and need to know the occurrences of changes and when these changes happen. If the primary table made any changes in such scenarios, we could create a trigger to insert the desired data into a separate table.

Types of Triggers 

There are 3 types of triggers in SQL Server:

1. Data Definition Language (DDL) triggers
2. Data Manipulation Language (DML) triggers
3. Logon triggers

1. Data Definition Language (DDL) trigger:

• DDL triggers are fired in the response to the DDL events, Like CREATE, ALTER, and DROP statements. We can create these triggers at the database level or server level, depending on the type of DDL events.
• DDL triggers fires only after the events are executed successfully. They cannot be used INSTEAD OF triggers.

The DDL triggers are useful in the following scenario:

• When we need to prevent the database schema from changing or modifying.
• When we need to Log changes made in the database schema.
• When we need to respond to a change made in the database schema.

Syntax:

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR { event_type | event_group }
AS 
    {sql_statement}

In the above syntax:

• trigger_name is the name of the new trigger being created.
• ON DATABASE specifies that the trigger is fired for DATABASE or ALL SERVER scoped events.
• ddl_trigger_option specifies the ENCRYPTION or EXECUTE AS clause. Encryption encrypts the trigger definition. EXECUTE AS defines the security context under which the trigger is executed.
• event_type specifies the event that causes the trigger to fire e.g., CREATE_TABLE, ALTER_TABLE, etc. The event_group is a group of event_type such as DDL_TABLE_EVENTS.

2. Data Manipulation Language (DML) triggers:

• DML triggers are fired in response to DML events like INSERT, UPDATE, and DELETE statements.
• It can also be executed in response to DML-like operations performed by system-defined stored procedures.

There are two types of DML Triggers:

1. After Triggers
2. Instead Of Triggers

1. After Triggers:

• AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully.
• All referential cascade actions and constraint checks must also succeed before these trigger fires.
• This trigger is executed when a table completes an insert, update or delete operation.
• It is not supported in views.
• Sometimes it is known as FOR triggers.

Syntax :

CREATE TRIGGER [schema.]trigger_name  
ON table_name  
AFTER  {INSERT, UPDATE, DELETE}  
[NOT FOR REPLICATION]  
AS  
{SQL_Statements}

Here,

schema: It is an optional parameter that defines which schema the new trigger belongs.

trigger_name: It defines the name of the trigger.

table_name: It defines the table name to which the trigger applies. Next to the table name, we need to write the AFTER clause and then after the list Events like INSERT, UPDATE, or DELETE. 

NOT FOR REPLICATION: This option tells that SQL Server does not execute the trigger when data is modified as part of a replication process.

SQL_Statements: It contains one or more SQL statements that are used to perform actions in response to an event that occurs.

Example :

Let us understand by the example.

First, we will create a ‘tbl_Employee‘ table by using the below statement.

CREATE TABLE tbl_Employee
(
  ID int IDENTITY(1,1) Primary Key,
  EmployeeName varchar(50) NULL,
  City varchar(50) NULL,
  Salary int NULL,
)

Now we will  insert the record into the Table as follows,

INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Mustakim','Valsad',1000)
INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Mihir','Surat',1000)
INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Vibha','Navsari',1000)
INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Priyank','Bardoli',1000)
INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Satish','Chikhli',1000)

A Record will be as follows,

We will also create another table named ‘tbl_Employee_Log’ to automatically store transaction records of each operation, such as INSERT,    UPDATE, or DELETE on the ‘tbl_Employee’ table 

CREATE TABLE tbl_Employee_Log
(
  ID int IDENTITY(1,1) Primary Key,
  Log_Description varchar(max) NULL,	
)

Now, we will create a trigger that stores the transaction Log of each inserted operation on the ‘tbl_Employee’ table into the ‘tbl_Employee_Log’ table. Here we are going to create the trigger as follows,

CREATE TRIGGER trg_Insert_Employee
ON tbl_Employee
AFTER INSERT
AS  
BEGIN  
  Declare @EmpId int  
  SELECT @EmpId = ID from inserted  
  INSERT INTO tbl_Employee_Log(Log_Description)
  VALUES ('New employee added with the Id = ' + CAST(@EmpId AS VARCHAR(10)) + ' on ' + CAST(Getdate() AS VARCHAR(22)))  
END

After creating a trigger, we will try to insert a record into the ‘tbl_Employee’ table:

INSERT INTO dbo.tbl_Employee(EmployeeName,City,Salary) values('Mustakim','Valsad',1000)

After Inserting a Record into the ‘tbl_Employee‘ table, Trigger will be fired and Log Record into the ‘tbl_Employee_Log‘ table. Now we will execute the SELECT statement and we can see the record Log as follows,

Now, we will create a trigger that stores the transaction Log of each Deleted operation on the ‘tbl_Employee’ table into the ‘tbl_Employee_Log’ table. Here we are going to create the trigger as follows,

CREATE TRIGGER trg_Delete_Employee
ON tbl_Employee
AFTER DELETE
AS  
BEGIN  
  Declare @EmpId int  
  SELECT @EmpId = ID from deleted  
  INSERT INTO tbl_Employee_Log(Log_Description)
  VALUES ('An Existing employee with the Id of ' + CAST(@EmpId AS VARCHAR(10)) + ' are Deleted on ' + CAST(Getdate() AS VARCHAR(22)))  
END

After creating a trigger, we will try to Delete a record from the ‘tbl_Employee‘ table:

Delete From dbo.tbl_Employee where ID=8

After Deleting a Record from the ‘tbl_Employee‘ table, a Trigger will be fired and Log Record into the ‘tbl_Employee_Log‘ table. Now we will execute the SELECT statement and we can see the record Log as follows,

In the above created both the triggers, you will notice these lines:

SELECT @EmpId = Id from inserted
SELECT @EmpId = Id from deleted

Here, inserted and deleted are special tables used by the SQL Server. The inserted table keeps the copy of the row when you insert a new row into the actual table. And the deleted table keeps the copy of the row you have just deleted from the actual table.

2. Instead Of Triggers:

• Instead of Trigger fires before SQL Server begins to execute the triggering operation that triggered it.
• Specifies that the DML trigger launches instead of the triggering SQL statement, thus, overriding the actions of the triggering statements.
• An Instead of trigger is fired instead of the triggering action such as an insert, update, or delete.
• You can’t specify INSTEAD OF for DDL or logon triggers.

CREATE TRIGGER trigger_name  
ON table_name  
INSTEAD OF {INSERT | UPDATE | DELETE}  
AS  
   BEGIN  
      -- trigger statements        
   END

3. Logon triggers:

• Logon triggers are fires in response to a LOGON event.
• The LOGON event occurs when a user session is generated with an SQL Server instance, which is made after the authentication process of logging is completed but before establishing a user session.
• If authentication fails, logon triggers do not execute.
• These triggers may be used to audit and control server sessions, such as tracking login activity or limiting the number of sessions for a particular login.

Syntax:

CREATE TRIGGER [trigger_name]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
   --statement
END

There are some cases where a logon trigger is useful as below,

• Restrict users to only log in at certain times.
• Restrict the number of concurrent sessions for a specific login
• Restrict the total number of connections.
• Restrict login by Hostname or IP Address
• Use a logon trigger for auditing

How To Delete Trigger in SQL Server?

• We can remove an existing trigger in SQL Server by using the DROP TRIGGER statement.
• We must be very careful while removing a trigger from the table. Because once we have deleted the trigger, it cannot be recovered.
• If a trigger is not found, the DROP TRIGGER statement throws an error.

The following syntax removes DML triggers:

DROP TRIGGER [IF EXISTS] schema_name.trigger_name; 
 
//OR Remove Multiple Trigger
DROP TRIGGER schema_name.trigger_name1, trigger_name2.....n;

Advantages of Triggers

• Triggers set database object rules and roll back if any change does not satisfy those rules. The trigger will inspect the data and make changes if necessary.
• Triggers help us to enforce data integrity.
• Triggers help us to validate data before being inserted or updated.
• Triggers help us to keep a log of records.
• Triggers increase SQL queries’ performance because they do not need to compile each time they are executed.
• Triggers reduce the client-side code which saves time and effort.
• Triggers are easy to maintain.

Disadvantages of Triggers

• Triggers only allow using extended validations.
• Triggers are invoked automatically, and their execution is invisible to the user. Therefore, it isn’t easy to troubleshoot what happens in the database layer.
• Triggers may increase the overhead of the database server.
• We can define the same trigger action for multiple user actions such as INSERT and UPDATE in the same CREATE TRIGGER statement.
• We can create a trigger in the current database only, but it can reference objects outside the current database.

I hope this article will help you to understand the concept of Trigger in SQL Server.

Thank You.