How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.

A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.

Disable Nesting/Recursing Triggers

Method 1:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Method 2:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Method 3:




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

Comma seperated date splitting without using loop.

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012  4:00AM,May 05 2012  4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                 ) strDt,charindex(',',CustomDates) as pos
       
    from @TableVar
    union all
    select s.ID,
        s.CustomDates,
       
             substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)       

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos


    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

---------------------------------------------------------------

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')


DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)

Normal query instead of MERGE operation

/****** Object:  StoredProcedure [dbo].[spBlockHr_SAVE]    Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Author    : Nikhildas
    Date    : July 01 2011 10:45AM
    Purpose : To Save Block HR value.
    Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL    OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By                On                Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    <Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
 ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
 (
    @P_XML        XML,
    @P_RET_VAL    INT OUTPUT
 )
 AS
    SET NOCOUNT ON

    DECLARE @DocHandle        INT
   
    DECLARE @BlockHr TABLE
    (
        ID            int ,
        SECTOR        nvarchar(255) ,
        Route        nvarchar(255) ,
        CODE        nvarchar(255) ,
        Month01     float ,
        Month02     float ,
        Month03     float ,
        Month04     float ,
        Month05     float ,
        Month06     float ,
        Month07     float ,
        Month08     float ,
        Month09     float ,
        Month10     float ,
        Month11     float ,
        Month12     float ,
        Total        float
    )   
           
    BEGIN TRY
        BEGIN TRANSACTION
        EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML
       
        INSERT INTO @BlockHr
        SELECT
            ID,
            SECTOR    ,
            Route,
            CODE,
            Month01    ,
            Month02    ,
            Month03 ,
            Month04    ,
            Month05    ,
            Month06    ,
            Month07    ,
            Month08    ,
            Month09    ,
            Month10    ,
            Month11    ,
            Month12    ,
            Total   
        FROM    OPENXML(@DocHandle,'/Root',2)
        WITH
        (
            ID            INT,
            SECTOR        NVARCHAR(1000),
            Route        NVARCHAR(1000),
            CODE        nvarchar(255),
            Month01        FLOAT(53),
            Month02        FLOAT(53),
            Month03        FLOAT(53),
            Month04        FLOAT(53),
            Month05        FLOAT(53),
            Month06        FLOAT(53),
            Month07        FLOAT(53),
            Month08        FLOAT(53),
            Month09        FLOAT(53),
            Month10        FLOAT(53),
            Month11        FLOAT(53),
            Month12        FLOAT(53),
            Total        FLOAT(53)

        )
                   
        EXEC sp_xml_removedocument @DocHandle
/*    Commented on 11-Jul-2011 (For Avoid Merge statement)       
        MERGE BlockHr AS T
        USING @BlockHr    AS S
        ON S.ID = T.ID
        WHEN MATCHED
        THEN
            UPDATE
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
        WHEN NOT MATCHED BY TARGET
        THEN
            INSERT
            (
                SECTOR    ,
                Route,
                CODE,
                Month01    ,
                Month02    ,
                Month03 ,
                Month04    ,
                Month05    ,
                Month06    ,
                Month07    ,
                Month08    ,
                Month09    ,
                Month10    ,
                Month11    ,
                Month12    ,
                Total           
            )
            VALUES
            (
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03 ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total   
            );
*/
            --// Updation
            UPDATE T
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
            FROM [BlockHr] T INNER JOIN @BlockHr S
            ON S.ID = T.ID

            --// Insertion
            INSERT INTO [BlockHr]
            SELECT                
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03    ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total        ,
                1
            FROM  [BlockHr] T RIGHT OUTER JOIN @BlockHr S
            ON S.ID = T.ID   
            WHERE (T.ID IS NULL)
                                           
                   
            EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
            SET @P_RET_VAL =   1
      COMMIT TRANSACTION
      SET @P_RET_VAL = 1
    END TRY
    BEGIN CATCH
        SET @P_RET_VAL = -1
        ROLLBACK TRANSACTION
    END CATCH