In this article, we will review triggers in SQL Server, different types of trigger events, trigger order and NOT FOR REPLICATION in triggers. A trigger is a database object that runs automatically when an event occurs. There are three different types of events.

• DML Events
• DDL Events
• LOGON Event – Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established

DML Triggers in SQL Server

DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user.

Creating triggers for a DML event

Let us create some sample tables and triggers in SQL Server.

We can create a DML trigger for a specific event or multiple events. The triggers in SQL Server(DML) fire on events irrespective to the number of rows affected.

Below is the sample syntax for creating a DML trigger for update event.

These triggers are created at the table level. Upon successful creation of trigger, we can see the triggers by navigating to Triggers folder at table level. Please refer to the below image.

Instead of triggers in SQL Server

These triggers are fired before the DML event and the actual data is not modified in the table.

For example, if we specify an instead of trigger for delete on a table, when delete statement is issued against the table, the instead of trigger is fired and the T-SQL block inside the triggers in SQL Server is executed but the actual delete does not happen.

T-SQL Syntax for creating an instead of trigger

• If there are multiple triggers along with instead of trigger on the table, the instead of trigger is fired first in the order
• INSTEAD of triggers can be created on views
• we can define only one instead of trigger per INSERT, UPDATE, or DELETE statement on a table or view

Enabling and disabling DML triggers on a table

Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.

Disabling specific SQL Server trigger on a table using T-SQL.

Enabling specific trigger on the table using T-SQL.

To enable all triggers on a table, use below syntax.

To disable all triggers on a table, use below syntax. This statement is not supported if the table is part of merge replication.

Dropping a trigger on a table.

To drop a DML trigger on the table using SQL Server management studio, navigate to the Triggers folder under the table. Select the table you want to drop, Right click on the trigger and click on Delete. Click Ok.

T-SQL to drop a trigger on the table.

Dropping a table will drop all the SQL Server triggers on the table along with the 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().

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.

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.

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

Enabling and disabling DDL triggers

Use below T-SQL syntax to disable or enable the DDL trigger at the database level.

Use below T-SQL syntax to drop a DDL trigger which is created at the database level.

LOGON Triggers in SQL Server

These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.

LOGON triggers are created at the server level and are useful below cases.

1. To audit login activity
2. To control the login activity

Creating LOGON triggers

You can use EVENTDATA() and write your custom code to track or control the connections. Here I am creating simple triggers in SQL Server for LOGON event. Below is the sample syntax for creating a LOGON trigger.

We must be cautious while creating these triggers as login may fail if the trigger execution fails or if you do not have access to objects referenced in the LOGON trigger. In such cases, the only member of the sysadmin role can connect to the server using a dedicated administrator connection. So, it is always better to enable dedicated administrator connection when using these triggers.

Enabling and disabling LOGON triggers

Use below T-SQL syntax to disable or enable the LOGON trigger.

Use below T-SQL syntax to drop a LOGON trigger.

Direct recursion

Direct recursion is a case where the SQL Server trigger on the table is fired and performs an action which again triggers the same trigger.

For example, please refer to below sample trigger for an update which is direct recursive.

Direct recursion can be controlled by a database setting RECURSIVE_TRIGGERS. If the setting is on, then the above trigger throws an error.

If the database setting RECURSIVE_TRIGGERS is off, then the trigger is fired only once and does not loop.

To change RECURSIVE_TRIGGERS setting using SSMS, navigate to the database, right click on the database and select Properties. Click on Options and change the setting to the option you want.

To set the RECURSIVE_TRIGGERS OFF using T-SQL, use below statement and replace the database name with your database name.

To set the RECURSIVE_TRIGGERS ON using T-SQL, use below statement and replace the database name with your database name.

Indirect Recursion

This is a case where a trigger is fired and invokes another trigger of the same type.

Below is the sample trigger for indirect recursion.

Now when we update a value in the table Temp1, the trigger TR_Temp1 is fired which updates Temp2 table. TR_Temp2 is fired and updates Temp1 table which causes TR_Temp1 to fire again.

This behavior can be controlled by setting nested triggers off.

SQL Server trigger order

SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers.

We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.

Below is the sample syntax for setting the order of trigger to first for the INSERT statement.

Now, when the data is inserted into the table “TriggerOrderTest” INSERT event occurs and the trigger TR_3 fires first.

In case DDL triggers we must specify the namespace parameter which is the scope of the SQL Server trigger in the stored procedure sp_settriggerorder.

Below is the sample syntax for setting the DDL trigger order.

NOT FOR REPLICATION

NOT FOR REPLICATION indicates that the trigger should not fire when the replication agent syncs the data changes to the subscriber.

For example, if you are replicating both Locations and LocationHist. Now when you update a record on Location the trigger is fired, inserts record in the history table. When these changes sync to another end (subscribers) there is no need of trigger to be fired again. So, if we mark the trigger for “NOT FOR REPLICATION” the trigger does not fire when replication agent sync’s the changes and fires only for the data changes done by the user.

Below is the sample syntax to create a triggers in SQL Server with not for replication.

If you want the triggers in SQL Server to be fired when the replication agent sync data changes to another end, just create the trigger without specifying “NOT FOR REPLICATION”.