sql server - My trigger is not calling recursively -

sql server - My trigger is not calling recursively -

below trigger , not called recursively please explain

use [testing] go /****** object: trigger [dbo].[updatetrigger] ******/ set ansi_nulls on go set quoted_identifier on go alter trigger [dbo].[updatetrigger] on [dbo].[test] update update dbo.test set lastediteddate=getdate() inserted newdata test.masterk = newdata.masterk

nesting triggers , trigger recursion:

by default, adaptive server allows nested triggers. prevent triggers nesting, utilize sp_configure set allow nested triggers alternative 0 (off):

sp_configure "allow nested triggers", 0

triggers can nested depth of 16 levels. if trigger changes table on there trigger, sec trigger fires , can phone call 3rd trigger, , forth. if trigger in chain sets off infinite loop, nesting level exceeded , trigger aborts, rolling transaction contains trigger query.

note: since triggers set transaction, failure @ level of set of nested triggers cancels entire transaction: info modifications rolled back. supply triggers messages , other error handling , debugging aids determine failure occurred.

the global variable @@nestlevel contains nesting level of current execution. each time stored procedure or trigger calls stored procedure or trigger, nesting level incremented. nesting level incremented 1 when cached statement created. if maximum of 16 exceeded, transaction aborts.

if trigger calls stored procedure performs actions cause trigger fire again, trigger reactivated if nested triggers enabled. unless there conditions within trigger limit number of recursions, causes nesting-level overflow.

for example, if update trigger calls stored procedure performs update, trigger , stored procedure execute 1 time if allow nested triggers off. if allow nested triggers on, , number of updates not limited status in trigger or procedure, procedure or trigger loop continues until exceeds 16-level maximum nesting value.

by default, trigger not phone call itself in response sec info modification same table within trigger, regardless of setting of allow nested triggers configuration parameter. set option, self_recursion, enables trigger fire 1 time again result of info modification within trigger. example, if update trigger on 1 column of table results in update column, update trigger fires 1 time when self_recursion disabled, can fire 16 times if self_recursion set on. allow nested triggers configuration parameter must enabled in order self-recursion take place.

sql-server tsql recursion database-trigger