In this blog we’ll look at Recursive triggers in SQL Server. Triggers are said to be recursive/nested when a trigger on a table calls another trigger on same or different table and so on. The total numbers of recursive calls that can be made are limited to 32.

Recursion can be enabled or disabled as shown below

Let’s see an example of recursive triggers. The below query creates two table tblone and tbltwo and two triggers trgone on tblone and trgtwo on tbltwo.

The trigger trgone is an insert trigger on tblone which inserts values from inserted table into tbltwo. The trigger tbltwo is another insert trigger on tbltwo. Thus, the trigger trgone will initiate the trgtwo on tbltwo. Let’s insert a value in trgone table.

1_Recursive triggers in SQL Server

As shown in above snapshot, an insert on tblone initiates trgone which in turn initiates trgtwo on tbltwo. If nested triggers are off, trgone will fire however trgtwo won’t and no error will be thrown.

Triggers nested level can be checked using TRIGGER_NESTLEVELfunction as shown below.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook