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. 

Create table Employee(id int NOT NULL PRIMARY KEY IDENTITY(1, 1), name varchar(50), email varchar(50));
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.

Insert into Employee(name, email) select 'John', '[email protected]';
Insert into Employee(name, email) select 'Sravan', ' [email protected]';
Insert into Employee(name, email) select 'AbdulRafay', '[email protected]';
Insert into Employee(name, email) select 'Suman', '[email protected]';
Insert into Employee(name, email) select 'Arun', '[email protected]';

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

CREATE TRIGGER yourTriggerName
ON DDLEventsName
FOR CREATE_DATABASE
AS
...............

Let us understand DDL triggers with an example

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

USE TestSampleDB
GO
CREATE TRIGGER DDLTriggerToBlockDropAlterTable
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
PRINT 'The DDLTriggerToBlockDropAlterTable does not allow you to drop or alter table'
ROLLBACK
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.

USE TestSampleDB
GO
CREATE TRIGGER DDLTriggerToBlockDropAlterTable
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
PRINT 'The DDLTriggerToBlockDropAlterTable does not allow you to drop or alter table'
ROLLBACK
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

CREATE TRIGGER ON TABLE_NAME
FOR DML_Events
AS
………………………………

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

CREATE TRIGGER tblEmployeeInsert on Employee
FOR INSERT
as
declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);
select @EmpId = Insrt.id, @EmpName = Insrt.name, @EmpEmail = Insrt.email from inserted Insrt;
insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)
values(@EmpId, @EmpName, @EmpEmail, 'Record inserted', GETDATE());
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.

CREATE TRIGGER tblEmployeeUpdate on Employee
FOR UPDATE
as
declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);
select @EmpId = Insrt.id, @EmpName = Insrt.name, @EmpEmail = Insrt.email from inserted Insrt;
insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)
values(@EmpId, @EmpName, @EmpEmail, 'Record update', GETDATE());
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

CREATE TRIGGER tblEmployeeDelete on Employee
FOR DELETE
as
declare @EmpId int, @EmpName varchar(50), @EmpEmail varchar(50);
select @EmpId = dltd.id, @EmpName = dltd.name, @EmpEmail = dltd.email from deleted dltd;
insert into AuditLog(EmpId, EmpName, EmpEmail, AuditAction, AuditTime)
values(@EmpId, @EmpName, @EmpEmail, 'Record delete', GETDATE());
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

CREATE TRIGGER ON TABLE_NAME
INSTEAD OF DML_Events
AS
………………………………

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

CREATE TRIGGER ConcurrentConnectionLimit
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'TestUser'
AND(SELECT COUNT( * ) FROM sys.dm_exec_sessions WHERE Is_User_Process = 1 AND Original_Login_Name = 'TestUser') > 2
BEGIN
PRINT 'You are not authorized to login, as you already have two active user sessions'
ROLLBACK
END
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.