Triggers In SQL Server

In this article, I will explain about triggers. Let us understand triggers with some examples. Let us create a sample table, as shown below. 
  1. Create table Employee(id int NOT NULL PRIMARY KEY IDENTITY(1, 1), name varchar(50), email varchar(50));  
  2. Create table AuditLog(id int NOT NULL PRIMARY KEY IDENTITY(1, 1), EmpId int, EmpName varchar(50), EmpEmail varchar(50), AuditAction varchar(50), AuditTime datetime);  

Let us now insert some mock data in table Employee.

  1. Insert into Employee(name, email) select 'John''john@test.com';  
  2. Insert into Employee(name, email) select 'Sravan'' Sravan@test.com';  
  3. Insert into Employee(name, email) select 'AbdulRafay''AbdulRafay@test.com';  
  4. Insert into Employee(name, email) select 'Suman''suman@test.com';  
  5. Insert into Employee(name, email) select 'Arun''arun@test.com';  

Let us now use the trigger’s concept. Basically, triggers are a special kind of stored procedure, which executes automatically when an event occurs in the database.

Triggers can be defined on the table, view, schema or a database with which the event is associated. 

Triggers can be used for calling a stored procedure. By using triggers, auditing can be done easily.

Types of triggers

There are four types of triggers,

  1. DDL Triggers
  2. DML Triggers
  3. CLR Triggers
  4. LOGON Triggers

DDL triggers

DDL stands for data definition language. We can create DDL triggers on DDL statements like create, alter, drop etc.

Syntax to create DDL triggers

  1. CREATE TRIGGER yourTriggerName  
  2. ON DDLEventsName  
  3. FOR CREATE_DATABASE  
  4. AS  
  5. ...............  

Let us understand DDL triggers with an example

Let us create a trigger when dropping and altering a table in the database.

  1. USE TestSampleDB  
  2. GO  
  3. CREATE TRIGGER DDLTriggerToBlockDropAlterTable  
  4. ON DATABASE  
  5. FOR DROP_TABLE, ALTER_TABLE  
  6. AS  
  7. BEGIN  
  8. PRINT 'The DDLTriggerToBlockDropAlterTable does not allow you to drop or alter table'  
  9. ROLLBACK  
  10. END  

The trigger given above does not allow us to alter/drop table ‘Employee’, as shown below.

 

You can use DDL_DATABASE_LEVEL_EVENTS, so that your trigger covers all DDL events, as shown below.

  1. USE TestSampleDB  
  2. GO  
  3. CREATE TRIGGER DDLTriggerToBlockDropAlterTable  
  4. ON DATABASE  
  5. FOR DDL_DATABASE_LEVEL_EVENTS  
  6. AS  
  7. BEGIN  
  8. PRINT 'The DDLTriggerToBlockDropAlterTable does not allow you to drop or alter table'  
  9. ROLLBACK  
  10. END  

DML triggers

DML stands for Data Manipulation Language. We can create DML triggers on DML statements like insert, update & delete. DML triggers are of two types, which are given below.

  • After trigger
  • Instead of Trigger

After trigger

This trigger will fire when SQL Server completed its execution of the action. For example, if you insert a row into the table and row has successfully inserted into the table, then AFTER trigger will execute. If an error occurs while inserting a row, the AFTER trigger will not fire.

In this trigger we use FOR clause or AFTER clause.

Syntax

  1. CREATE TRIGGER ON TABLE_NAME  
  2. FOR DML_Events  
  3. AS  
  4. ………………………………  

Let us understand with an example:

Let us create an After trigger for the table ‘Employee’.

When record is inserted

Let’s create a trigger for an insert

  1. CREATE TRIGGER tblEmployeeInsert on Employee  
  2. FOR INSERT  
  3. as  
  4. declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);  
  5. select @EmpId = Insrt.id, @EmpName = Insrt.name, @EmpEmail = Insrt.email from inserted Insrt;  
  6. insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)  
  7. values(@EmpId, @EmpName, @EmpEmail, 'Record inserted', GETDATE());  
  8. print 'Triggered fired - After Insert'  
Here, whenever any insert happens in the table, the trigger associated with the table will fire. The trigger will store the inserted records in the inserted table.

Let us execute insert command.



When the record is updated

Let’s create a trigger for an update.

  1. CREATE TRIGGER tblEmployeeUpdate on Employee  
  2. FOR UPDATE  
  3. as  
  4. declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);  
  5. select @EmpId = Insrt.id, @EmpName = Insrt.name, @EmpEmail = Insrt.email from inserted Insrt;  
  6. insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)  
  7. values(@EmpId, @EmpName, @EmpEmail, 'Record update', GETDATE());  
  8. print 'Triggered fired - After Update'  

Here, whenever any update happens in the table, the trigger associated with the table will fire. The trigger will store the updated records in the inserted table.

Let us execute update command

 

When the record is deleted

Let’s create a trigger for delete

  1. CREATE TRIGGER tblEmployeeDelete on Employee  
  2. FOR DELETE  
  3. as  
  4. declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);  
  5. select @EmpId = dltd.id, @EmpName = dltd.name, @EmpEmail = dltd.email from deleted dltd;  
  6. insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)  
  7. values(@EmpId, @EmpName, @EmpEmail, 'Record delete', GETDATE());  
  8. print 'Triggered fired - After Delete'  

Here, whenever any deletion happens in the table, the trigger associated with the table will fire. The trigger will store the deleted record in the deleted table.

Let us execute delete command

 

Instead of trigger

This trigger will fire before SQL Server starts its execution of the action. For example, if you insert a row into the table, before the row gets inserted into the table, the trigger associated with the insert event will fire. If an error occurs while inserting a row, the INSTEAD trigger will fire.

In this trigger, we use INSTEAD OF clause.

Syntax

  1. CREATE TRIGGER ON TABLE_NAME  
  2. INSTEAD OF DML_Events  
  3. AS  
  4. ………………………………  
For the detailed example, you can refer the examples given above described for “AFTER/FOR” triggers. Just replace FOR clause with INSTEAD OF clause.

CLR Triggers

CLR stands for Common Language Runtime. CLR triggers can be created by any .NET framework language, because of SQL Server integration with .NET framework CLR. For more details, please refer CLR Triggers

Logon triggers

If you want to track the login activity or to limit the number of the user session for a specific login, then LOGON trigger is used. LOGON triggers are fired when LOGON event of SQL server is raised. For more details, please refer LOGON trigger

Example

  1. CREATE TRIGGER ConcurrentConnectionLimit  
  2. ON ALL SERVER WITH EXECUTE AS 'sa'  
  3. FOR LOGON  
  4. AS  
  5. BEGIN  
  6. IF ORIGINAL_LOGIN() = 'TestUser'  
  7. AND(SELECT COUNT( * ) FROM sys.dm_exec_sessions WHERE Is_User_Process = 1 AND Original_Login_Name = 'TestUser') > 2  
  8. BEGIN  
  9. PRINT 'You are not authorized to login, as you already have two active user sessions'  
  10. ROLLBACK  
  11. END  
  12. END  

Now, when you try to open more than 2 concurrent sessions for the ‘TestUser’, you will get the message given below.



This is all about triggers.