Triggers in SQL Server

In this post, I will show you what are triggers and how we can use triggers in SQL server.

SQL Server triggers are special stored procedures that are executed automatically in response to the database object, database, and server events. SQL Server provides three type of triggers:

• Data manipulation language (DML) triggers which are invoked automatically in response to INSERT, UPDATE, and DELETE events against tables.
• Data definition language (DDL) triggers which fire in response to CREATE, ALTER, and DROP statements. DDL triggers also fire in response to some system stored procedures that perform DDL-like operations.
• Logon triggers which fire in response to LOGON events

A SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance. There are three main characteristics that make triggers different than stored procedures:

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

DML Triggers

DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers are fired, when ever data is modified using INSERT, UPDATE, and DELETE events.

DML triggers can be again classified into 2 types.

1. After triggers (Sometimes called as FOR triggers)
2. Instead of triggers

After triggers, as the name says, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution.

On ther hand, as the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.

We will use tblEmployee and tblEmployeeAudit tables for our examples

SQL Script to create tblEmployee table:

CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)

Insert data into tblEmployee table
Insert into tblEmployee values (1,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)

tblEmployee



SQL Script to create tblEmployeeAudit table:
CREATE TABLE tblEmployeeAudit
(
  Id int identity(1,1) primary key,
  AuditData nvarchar(1000)
)

When ever, a new Employee is added, we want to capture the ID and the date and time, the new employee is added in tblEmployeeAudit table. The easiest way to achieve this, is by having an AFTER TRIGGER for INSERT event.

Example for AFTER TRIGGER for INSERT event on tblEmployee table:

CREATE TRIGGER tr_tblEMployee_ForInsert
ON tblEmployee
FOR INSERT
AS
BEGIN
 Declare @Id int
 Select @Id = Id from inserted
 
 insert into tblEmployeeAudit 
 values('New employee with Id  = ' + Cast(@Id as nvarchar(5)) + ' is added at ' + cast(Getdate() as nvarchar(20)))
END

In the trigger, we are getting the id from inserted table. So, what is this inserted table? INSERTED table, is a special table used by DML triggers. When you add a new row into tblEmployee table, a copy of the row will also be made into inserted table, which only a trigger can access. You cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of tblEmployee table.

So, now if we execute the following INSERT statement on tblEmployee. Immediately, after inserting the row into tblEmployee table, the trigger gets fired (executed automatically), and a row into tblEmployeeAudit, is also inserted.

Insert into tblEmployee values (7,’Tan’, 2300, ‘Female’, 3)

Along, the same lines, let us now capture audit information, when a row is deleted from the table, tblEmployee.
Example for AFTER TRIGGER for DELETE event on tblEmployee table:

CREATE TRIGGER tr_tblEMployee_ForDelete
ON tblEmployee
FOR DELETE
AS
BEGIN
 Declare @Id int
 Select @Id = Id from deleted
 
 insert into tblEmployeeAudit 
 values('An existing employee with Id  = ' + Cast(@Id as nvarchar(5)) + ' is deleted at ' + Cast(Getdate() as nvarchar(20)))
END

The only difference here is that, we are specifying, the triggering event as DELETE and retrieving the deleted row ID from DELETED table. DELETED table, is a special table used by DML triggers. When you delete a row from tblEmployee table, a copy of the deleted row will be made available in DELETED table, which only a trigger can access. Just like INSERTED table, DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of tblEmployee table.

DDL Triggers

DDL triggers in SQL Server are fired on DDL events. i.e. against create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event.

These triggers are useful in the below cases.

• Prevent changes to the database schema
• Audit database schema changes
• To respond to a change in the database schema

Creating a DDL trigger

Below is the sample syntax for creating a DDL trigger for ALTER TABLE event on a database which records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().

CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)
 
CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
 
INSERT INTO TableSchemaChanges
SELECT EVENTDATA(),GETDATE()
 
END

SQL Server trigger(DDL) on database

You can specify an event group which consists of different DDL events. If we specify an event group while creating a DDL trigger, the trigger is fired when a DDL event in the group occurs.

For example, if we want to create a trigger for all DDL events at the database level, we can just specify the DDL_DATABASE_LEVEL_EVENTS event group as shown in the below image.

DDL trigger for all database level ddl events
To view database level triggers, Login to the server using SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers.

DDL trigger at database level
To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder.

SQL Server trigger – Server level
Enabling and disabling DDL triggers
Use below T-SQL syntax to disable or enable the DDL trigger at the database level.

ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
 
DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
Use below T-SQL syntax to drop a DDL trigger which is created at the database level.

1
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE

Logon Triggers

The Logon Triggers in SQL Server are the special kind of stored procedure or we can also say a special type of operation which fire or executed automatically in response to a LOGON event and moreover, we can define more than one Logon trigger on the server.

The Logon triggers are fired only after the successful authentication but before the user session is actually established. If the authentication is failed then the logon triggers will not be fired.

Why we need Logon Trigger in SQL Server?

The Logon Triggers in SQL Server are commonly used to audit and control the server sessions such as

1. Tracking the Login Activity
2. Limiting the number of concurrent sessions for a single user
3. Restricting logins to SQL Server based on time of day, hostnames, application names

You can restrict a user from opening more than 5 connections with the server at the same time.

 CREATE TRIGGER Connection_Limit_tr  
 ON ALL SERVER WITH EXECUTE AS 'sa'  
 FOR LOGON  
 AS  
 BEGIN  
 IF ORIGINAL_LOGIN() <> 'sa'  
 AND  
 ( SELECT COUNT(*)  
 FROM sys.dm_exec_sessions  
 WHERE Is_User_Process = 1 AND  
 Original_Login_Name = ORIGINAL_LOGIN()  
 ) > 1  
 ROLLBACK  
 END 

“When” limits the connections for all Logins except ‘sa’. Once the connection limit is reached the user will not be able to create a new connection.