There are some scenarios to deal with Recursive triggers.
For suppose, you have one trigger for all DML operations ( INSERT, UPDATE and DELETE) on table and the trigger logic is doing one update operation on same table with respect to each INSERT operation.
CREATE TRIGGER TriggerName ON tableA
FOR INSERT, UPDATE, DELETE
AS
BEGIN
if Operation is INSERT
--DO an UPDATE on same table
else if it is UPDATE
-- DO an INSERT on same table
else
-- DO an logging operation
END
Due to the one round of INSERT/UPDATE operation inside a trigger, it will do recursive triggering...
Now, if you really need the recursive ness of this trigger, you can enable by setting
RECURSIVE_TRIGGERS
ON ; else you can prevent this behaviour by setting RECURSIVE_TRIGGERS
OFF at database-level. By default, the RECURSIVE_TRIGGERS is OFF...
ALTER DATABASE DatabaseName
SET RECURSIVE_TRIGGERS ON
ALTER DATABASE DatabaseName
SET RECURSIVE_TRIGGERS OFF