will trigger for UPDATE on the Table will be recursive

Question

I have trigger UPDATETRIGGER on table TEST,

It was written to get called when the TEST table is updated.

Now in this UPDATETRIGGER is updating a column of the same TEST table.

Will this be recursive?

My trigger and table is in MS SQL database. From the table values i see that it is not happening such way could any one explain please.

USE [TESTING]
GO
/****** Object:  Trigger [dbo].[UPDATETRIGGER] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UPDATETRIGGER] on [dbo].[TEST]
 FOR UPDATE 
 AS
  UPDATE dbo.TEST
    SET lastEditedDate=GetDate()
    FROM INSERTED newdata
    WHERE TEST.MasterK = newdata.MasterK

Lipfert

Answer 1

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers in SQL Server supports the nesting of triggers up to a maximum of 32 levels.

Nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers is when a trigger fires and performs a statement that will cause the same trigger to fire.

Disabling Nesting/Recursing Triggers: The following script will stop executing all the nested triggers.

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

There is also alternate way to stop Trigger Recursion:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting to certain level Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ( TRIGGER_NESTLEVEL(OBJECT_ID(@@PROCID)) > 5 )
    RETURN;

ref:- http://blog.sqlauthority.com/2007/05/18/sql-server-2005-understanding-trigger-recursion-and-nesting-with-examples/

Dilisio

reference...? looks like plagiarism to me. You basically copied the article verbatim. – Solitaire

yeah i copied the article, because this seems a solution to the question. if user take some pain to do the Google before asking here then we do not have to do this like copy paste. :) – Dilisio

anubrij Chandra , when we do sp_CONFIGURE , we get all the information of trigger recursive so in that I have minimum and maximum could you explain what is that and how can we edit that value – Lipfert

Answer 2

MS SQL has some properties set that do not allow recursive triggers to fire unless you turn them on. Well it will fire the recursive / nested trigger 32 times and then fail out. Also this is a for update trigger not a before/after update trigger so this takes place of the update functionality itself.

After (For) / Before

These two types create functionality on top of what the database will actually do in an update. So if the before or after changes information the databases original update function will fire again and then you are well into your loop.

Instead Of

This overrides the database's normal functionality and does only what you tell it to do when an update occurs. In a database update is actually the combination of Delete/Insert. That blew my mind the first time I realized that as well.

Solitaire

Thank you very very much Damon , I am completely new to trigger, Could you please explain more why this is set once only. – Lipfert

Damon you are saying that my trigger which above only execute once , irrespective of RECURSIVE TRIGGER IS ON OR OFF – Lipfert

I was wrong on that one... I had a type confused. Sorry, been awhile since I looked at triggers. Change that one to a "INSTEAD OF" and you will be fine. – Solitaire