Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

will trigger for UPDATE on the Table will be recursive

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
like image 295
Sudhakar Kummarasetty Avatar asked Nov 11 '14 06:11

Sudhakar Kummarasetty


People also ask

Are SQL triggers recursive?

Recursive triggers were introduced in SQL Server 7.0. If a trigger modifies the same table where the trigger was created, the trigger does not fire again unless the recursive triggers option is turned on. recursive triggers is a database option turned off by default.

What triggers recursive?

A recursive trigger is one that performs an action, such as an update or insert, which invokes itself owing to, say something like an update it performs. Recursion is the process of executing the same task multiple times. There may be chances to hit the Governor Limit with Recursive Trigger.

How does after update trigger work?

AFTER UPDATE Trigger is a kind of trigger in SQL that will be automatically fired once the specified update statement is executed. It can be used for creating audit and log files which keep details of last update operations on a particular table.

Can we use two triggers on same table before after update?

You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created. Db2 records the timestamp when each CREATE TRIGGER statement executes.


1 Answers

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 ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

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

like image 166
Anubrij Chandra Avatar answered Sep 28 '22 19:09

Anubrij Chandra