What is a SQL Server Trigger?

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.

The fact that it's impossible to use parameters on triggers is not a limitation to receive information from the firing event. As you will see further on, there are alternatives to obtain information about the firing event.

In SQL server there are 3 types of triggers
1. DML triggers 
2. DDL triggers
3. Logon trigger

1. DML (Data Modification Language) triggers:

    This is the most used class of 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.  In this case the firing event is a data modification statement; it could be an insert, update or delete statement either on a table or a view.

DML triggers can be again classified into 2 types.

1. After triggers (Sometimes called as FOR 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.

2. Instead of triggers
    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. This is very useful in cases where you need to have cross database referential integrity.



2. DDL (Data Definition Language) triggers:
    
    This class of triggers fires upon events that change the structure (like creating, modifying or dropping a table), or in certain server related events like security changes or statistics update events. DDL triggers fire in response to DDL events - CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc...). Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. Example - sp_rename system stored procedure

What is the use of DDL triggers

  • If you want to execute some code in response to a specific DDL event
  • To prevent certain changes to your database schema
  • Audit the changes that the users are making to the database structure

Syntax for creating DDL trigger

CREATE TRIGGER [Trigger_Name]
ON [Scope (Server|Database)]
FOR [EventType1, EventType2, EventType3, ...],
AS
BEGIN
   -- Trigger Body
END


DDL triggers scope : DDL triggers can be created in a specific database or at the server level. 

The following trigger will fire in response to CREATE_TABLE DDL event. 

CREATE TRIGGER trMyFirstTrigger
ON Database
FOR CREATE_TABLE
AS
BEGIN
   Print 'New table created'
END



3. Logon trigger

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 

Logon triggers can be used for
1. Tracking login activity
2. Restricting logins to SQL Server
3. Limiting the number of sessions for a specific login

Logon trigger example : The following trigger limits the maximum number of open connections for a user to 3. 

CREATE TRIGGER tr_LogonAuditTriggers
ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @LoginName NVARCHAR(100)

    Set @LoginName = ORIGINAL_LOGIN()

    IF (SELECT COUNT(*) FROM sys.dm_exec_sessions
         WHERE is_user_process = 1
         AND original_login_name = @LoginName) > 3
    BEGIN
         Print 'Fourth connection of ' + @LoginName + ' blocked'
         ROLLBACK
    END

END


What is The Importance of SQL Server Triggers?

One of the fundamental characteristics of relational databases is data consistency. This means that the information stored in the database must be consistent at all times for every session and every transaction. The way relational database engines like SQL Server implement this is by enforcing constraints like primary keys and foreign keys. But sometimes that is not enough.

In SQL Server there is no chance to enforce referential integrity between two tables using foreign keys if those tables are in different databases or different servers. In such case the only way you can implement it is by using triggers.