Is it possible Looping in trigger

Question

My Trigger is on the table affected here:

UPDATE TABLE_NAME SET VALUE = @VALUE

now i execute the above query means trigger will execute now

TRIGGER
AFTER UPDATE
AS
BEGIN
.
.
UPDATE TABLE_NAME SET VALUE = @VALUE 
.
.
END

now 2nd update query execute means again trigger will execute or not this is my question.

Answer 1

Maybe

In SQL Server, this is "direct trigger recursion"

This is controlled by 2 things

• "nested triggers" option at the server level which controls all trigger recursion.
  See MSDN: http://msdn.microsoft.com/en-gb/library/ms178101.aspx
• RECURSIVE_TRIGGERS at the database level which controls direct recursion. See http://msdn.microsoft.com/en-us/library/bb522682.aspx

For full details, see Create Nested Triggers on MSDN

Note that this will loop until @@NESTLEVEL reaches 32