Introduction of Triggers in SQL Server

Triggers are the database objects that are used in relational databases. These are executed or fired automatically, when DDL or DML command statement is executed related to trigger. Triggers are used to assess or evaluate data, preserve data integrity, control server operations, used to implement business logic/rules, or to audit server, etc., very easily. One type of trigger i.e. even, allows modifying a data to multiple base tables of a view.

I will explain the different types of triggers used in SQL Server and more about their respective roles.

Types of Triggers:

There are four types of triggers used in SQL server:

1. DDL Triggers (Data Definition Language Triggers)
2. DML Triggers (Data Manipulation Language Triggers)
3. CLR Triggers
4. Logon Triggers

DDL Triggers:

Users can create triggers on DDL statements like Alter, Create, Drop, etc., and stored procedures based on a system that perform DDL operations.

Example: Suppos, a user executes the Create Login statement or sp_addlogin stored procedure for user login, then both these procedures can fire a DDL trigger that user can create on the create_login event.

We can also make After Trigger on DDL statements and cannot use Instead of the clause.

DDL triggers are helpful to manage administrator tasks like; regulating auditing database operations used to control actions on the SQL Server.

DML Triggers

In SQL Server we can create triggers on DML statements (like; INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types:

1. After Trigger:  When execution of the action performed on SQL Server is about to finish, then this trigger is fired. For example: When a user insert record in a table, then the trigger related to this insert event will fire only after the ‘all constraints pass in the row’, like; primary key, rules, etc. If it fails then SQL Server won’t fire this trigger.

2. Instead of Trigger:

This trigger starts before the execution of the action performed by SQL Server. It used before the action is performed opposite to the after trigger. The user can apply Instead of clause like; update, deleted, insert triggers for a table. Actually, it doesn’t include insert and all to the table.

For example: When a user insert record in a table, then the trigger related to this insert event will fire only before the ‘all constraints passed’ in the row like primary key, rules, etc. If it fails then SQL server will fire this trigger.

3. CLR Triggers

These are the special types of triggers that are based on Common Languages Runtime in .net framework. CLR is integrated with the SQL Server 2008 and allows triggers to be programmed in .net languages like; VB, C# etc.

User can write code for both DDL and DML triggers using CLR languages. For more on CLR triggers visit

Logon Triggers

These triggers are used when a LOGON event of the SQL Server occurs. This event gets raised when a user session is being established with the SQL Server after the authentication phase has finished. All the messages defined in the trigger like; error messages will be redirected to the error log of the SQL Server. If authentication fails, then the logon triggers aren’t being fired. To audit server controls and server sessions like login activity, limit the no. of sessions etc., these triggers has used.

Syntax to use The Logon Trigger

Create trigger trigger_name

On all server

[with encryption]

{For|after} logon

As

Sql_statement [1..n]

Syntax for trigger

Create trigger trigger_name

On {table|view}

[with encryption|Execute As] -- this is optional, if specified, then text in the trigger will be encrypted.

{ for|after|instead of } { [create|alter|drop|delete|update|insert] }

[not for replication] – It indicates that trigger shouldn’t be executed when a table is modified by the replication process.

As – After this action and condition are performed.

SQL_statement upto n

Conclusion: All I have explained above is about triggers used in SQL Server and their types. I also described how to code triggers for the default table or view in the SQL Server. For more details about the triggers and their execution, I will explain in detail on my next blog.