Will cause the UPDATE on the table to be recursive
I have an UPDATETRIGGER trigger on a TEST table ,
It was written to be called when the TEST table was updated.
Now in this UPDATETRIGGER a column of the same TEST table is being updated .
Will it be recursive?
My trigger and table are in MS SQL database. From the value table I can see it does not happen this way, can someone 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
source to share
Trigger events can be fired as part of another trigger action. One trigger execution can run even on another table or in the same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers in SQL Server support trigger nesting up to a maximum of 32 levels.
Nesting means that when a trigger fires, it also causes another trigger to fire. If the trigger creates an infinite loop, the nesting level 32 will be exceeded and the trigger will be canceled with an error message. Recursive triggers are when a trigger fires and executes a statement that will cause the same trigger to fire.
Disable Nesting / Recursing Triggers: The following script will terminate all nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
There is also an alternative way to stop the trigger recursion:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Limiting the trigger to a specific level Place the following script in your startup code. This will stop trigger recursion after certain levels. In the next case, it will stop after 5 recursions.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
source to share
MS SQL has a set of properties that prevent recursive triggers from firing unless you enable them. Well, it will fire a recursive / nested trigger 32 times and then crash. Also, it is an update trigger, but not a pre / post update trigger, so it comes from the update function itself.
After (For) / Before
These two types build functionality on top of what the database will actually do when updated. So if before or after the change the information starts working again with the original update of the databases, then you will end up in your loop.
Instead
This overrides the normal database functionality and does just what you tell it to do on upgrade. In a database update, this is actually a Delete / Insert combination. It blew my mind the first time I realized it.
source to share