Recursive Trigger in SQL Server

by
Recursive trigger example

This post will discuss the Recursive Trigger in SQL Server. We will discuss and draw lessons from several cases to help you comprehend the subject better. The whole list of subjects we’ll discuss is provided below.

  • Recursive Trigger in SQL Server
  • Recursive Triggers Enabled SQL Server
  • How to avoid Recursive Trigger in SQL Server

Also, check the latest tutorial on SQL Server: SQL Server Outer Join With Count

Recursive Trigger in SQL Server

Here we will learn and understand how to use the RECURSIVE TRIGGER in the SQL Server in detail, which will be explained with the help of an illustrated example.

When a trigger on one table invokes another trigger on a different or related table, and so on, that trigger is said to be recursive or nested. There is a cap of 32 on the total number of recursive calls that can be done.

There are two types of recursion:

  • DIRECT RECURSION
  • INDIRECT RECURSION

DIRECT RECURSION:-

When a trigger fires and then execute an action that sets off another trigger, this recursion normally occurs.

When the same trigger is called once more but after another trigger of a different type (AFTER or INSTEAD OF) is called, direct recursion can also happen. In other words, even if one or more AFTER triggers are activated between calls to the same INSTEAD OF trigger, direct recursion of the INSTEAD OF trigger can still happen. In the same way, even if one or more INSTEAD OF triggers are called in between an AFTER trigger’s direct recursion can happen when it is invoked a second time.

INDIRECT RECURSION:-

When a trigger activates and completes an action that prompts another trigger of the same type (AFTER or INSTEAD OF) to activate, a recursive cycle is created. The first trigger fires once more as a result of the second trigger’s activity.

In other words, when an INSTEAD OF trigger is triggered a second time, indirect recursion can happen, but not after another INSTEAD OF trigger is called in between. When an AFTER trigger is called again, but not before another AFTER trigger is called in between indirect recursion can also happen.

Here is an example of how to disable and enable recursive trigger in SQL Server by the following query:

ENABLE or DISABLE EXAMPLE:

ALTER DATABASE SQLSERVERGUIDES
SET RECURSIVE_TRIGGERS ON;

In this preceding query, we have used the ALTER DATABASE statement on the SQLSERVERGUIDES database to set the recursive_trigger as ON in the system. If the user wants to switch off the recursive_trigger as OFF then all the user has to do is the RECURSIVE_TRIGGERS in the system by using the OFF keyword.

In SQL Server 7.0, RECURSIVE TRIGGER was introduced. Unless the RECURSIVE TRIGGER option is enabled, a trigger that alters the same table where the trigger was generated does not fire again. Recursive triggers are a database setting that is by default disabled.

Recursive trigger example
Example of Recursive Trigger in SQL Server

We hope that you have understood the subtopic “Recursive Trigger in SQL Server” by using the SQL Server ALTER TABLE statement to set the recursive trigger to enable on the database. For a better understanding, we have used an example and explained it in depth.

Read: SQL Server Trigger If Exists Raise Error

Recursive Triggers Enabled SQL Server

Here we will learn and understand how to enable recursive trigger in the SQL Server, which is explained with the help of syntax and illustrated example.

SYNTAX:

ALTER DATABASE YOUR_DATABASE_NAME
SET RECURSIVE_TRIGGER [ON | OFF];

EXAMPLE:

ALTER DATABASE SQLSERVERGUIDES
SET RECURSIVE_TRIGGER ON;

As we see in the above query, the ALTER DATABASE statement has been used on the SQLSERVERGUIDES to set the RECURSIVE_TRIGGER with the ON keyword. This means that one trigger on one table invokes another trigger on another table will be invoked or recursive.

We hope that you have understood the subtopic “Recursive Trigger Enabled in SQL Server” by using the SQL Server ALTER DATABASE statement. For a better explanation, we have used an example and explained it in depth.

Read: How to execute Trigger in SQL Server

How to avoid Recursive Trigger in SQL Server

Here we will understand the subtopic “How to avoid Recursive Trigger in SQL Server” in detail.

If we want to avoid the SQL Server Recursive Trigger in the SQL Server by disabling the recursion. This will prevent a trigger to fire another trigger or itself again. Here is an example to execute this command:

EXAMPLE:

USE SQLSERVERGUIDES;

ALTER DATABASE SQLSERVERGUIDES
SET RECURSIVE_TRIGGERS OFF;

CREATE TRIGGER INSTEADOF_DELETE
ON CANADA_STATES
INSTEAD OF DELETE 
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=95;

DELETE FROM CANADA_STATES
WHERE FULL_NAME='Emma Watson';

As we see in the above query, we have used the ALTER statement on the SQLSERVERGUIDES database to set the RECURSIVE_TRIGGERS in the OFF mode. This query will not trigger another trigger or itself again.

Then we created a trigger called INSTEADOF_DELETE on the CANADA_STATES table by using CREATE TRIGGER statement. Then we used the INSTEAD OF trigger on the DELETE statement which means that once the INSTEAD OF trigger is fired then the DML STATEMENT execution will be skipped and will make no effect on the CANADA_STATES table. Instead, the SQL STATEMENT will be executed to show the result set.

In the AS clause, the SELECT statement will retrieve all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 95 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CANADA_STATES table otherwise vice-versa.

In the DELETE statement, it will delete one record from the CANADA_STATES which is used with the WHERE condition. In the WHERE condition, the FULL_NAME column is used with the EQUAL TO operator to find a string_name as Emma Watson from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the DELETE statement will delete records from the CANADA_STATES table which is based on the WHERE condition.

The DELETE statement has been executed after the INSTEAD OF trigger is fired. But if the RECURSIVE_TRIGGER was set to ON then it will call another trigger or itself again.

How to avoid recursive trigger in Sql server example
Example of How to avoid Recursive Trigger in SQL Server

We hope that you have understood the subtopic “How to avoid Recursive Trigger in SQL Server” by using SQL Server TRIGGER on the table by the query. For a better illustration, we have used an example and explained it in depth.

Also, take a look at some more SQL Server tutorials.

We now know how to use Recursive Trigger in SQL Server statements after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • Recursive Trigger in SQL Server
  • Recursive Triggers Enabled SQL Server
  • How to avoid Recursive Trigger in SQL Server
  • Recursive Trigger in SQL Server Example