none
How to kill a trigger stuck in an infinite loop RRS feed

  • Question

  • Long story short, I created a trigger that I fired and it is now seemingly stuck in an infinite loop.

    How do I kill the trigger or interrupt it?

     

    Thanks

     

    P.S. The code is attached below for the trigger in case anyone also can tell me why this trigger is stuck.

     

    Code Snippet

    -- ================================================================================

    -- ********************************************************************************

    -- Author: Colin Thompson - USCTho

    -- Create date: 11/07/08

    -- Version: 1.0

    -- Description: When a manifest is imported into the Project table, this trigger will

    -- build the test entries in the GenTest table using the tests listed

    -- in the GenCategories table

    -- ********************************************************************************

    -- ================================================================================

    CREATE TRIGGER tr_CreateGenTest_i ON Project FOR INSERT

    AS

    DECLARE @rc AS INT; --Used to store the value returned by @@rowcount (# of rows affected by INSERT)

    SET @rc = @@rowcount;

    --If zero rows were affected, do nothing & exit

    IF @rc = 0 RETURN;

    --Declare Variables

    DECLARE @RowID AS INT,

    @GenUnitID AS VARCHAR(10),

    @ProjectID AS VARCHAR(50),

    @rcGenTest AS INT,

    @rcTests AS INT,

    @rcGenUnits AS INT,

    @TestID AS VARCHAR (5),

    @i AS INT;

    --Populate the variable with the value of the Project ID just inserted

    SELECT @ProjectID = ProjectID FROM inserted;

    --Build a temporary table containing the different MCP's for this ProjectID

    SELECT MCP, ProjectID INTO #s

    FROM Unit WHERE ProjectID = @ProjectID

    GROUP BY MCP, ProjectID;

    --Create Temporary table with RowID

    CREATE TABLE #t (

    RowID TINYINT IDENTITY(1,1) NOT NULL,

    MCP VARCHAR (10),

    ProjectID VARCHAR (50))

    --Insert MCP and ProjID data into the Temp Table #t which will also auto-create the RowID values

    INSERT INTO #t (MCP, ProjectID)

    Select MCP, ProjectID from #s;

    -- See how many test combinations we need to create per UnitID

    SELECT @rcTests = COUNT(*) FROM GenCategories

    SELECT @rcGenUnits = COUNT (*) FROM #t

    --Pre-load the variables

    SELECT @RowID = RowID, @GenUnitID = MCP, @ProjectID = ProjectID

    FROM (SELECT TOP (1) RowID, MCP, ProjectID

    FROM #t

    ORDER BY RowID) AS D;

    --Each iteration of this loop, the code will grab the data from the temp table #t

    -- and build the GenTest table rows for all MCP's in the Project just added

    WHILE @@rowcount >0

    BEGIN

    --Initialise i

    SET @i=1

    --This loop INSERTs every possible test for a single unit into the GenTest table

    --Each iteration of this loop, the code will grab the data from the temp table #t

    -- and build the GenTest table rows for all MCP's in the Project just added

    WHILE @i<=@rcTests --While there are more combinations to be made

    BEGIN

    --Build a row into the UnitTest table

    SELECT @TestID = GenCatID

    FROM GenCategories WHERE RowID = @i

    INSERT INTO GenTest (GenUnitID, idGenCat, ProjectID)

    VALUES (@GenUnitID, @TestID, @ProjectID)

    -- Increment i

    SET @i=@i+1

    END

    --Grab the values from the next row in the Temp Table

    SELECT @GenUnitID = MCP, @ProjectID = ProjectID

    FROM (SELECT TOP (1) MCP, ProjectID

    FROM #t

    WHERE RowID > @RowID

    ORDER BY RowID) AS D;

    END

    DROP TABLE #t

    DROP TABLE #s

    GO

     

     

    Tuesday, November 11, 2008 3:55 PM

Answers

All replies

  • Hi,

     

    Have you attempted to disable the Trigger in question?

     

    Simply navigate to the Trigger within SQL Server Management Studio, right click and select disable.

     

    Or in SQL execute:

     

    Code Snippet

    DISABLE TRIGGER tr_CreateGenTest_i ON Project ;

    GO

     

     

    Once disabled you need to identify the query that is stuck in an infinite loop. Execute the following code in order to identify the process responsible or use SSMS Activity monitor within the Management folder.

     

    Code Snippet
    sp_who2 'Active'

     

     

     

    To terminate a particular thread execute the kill statement followed by the appropriate process ID.

     

    Code Snippet
    KILL 24

     

     

    Let me know how you get on.

     

    Cheers,

    Tuesday, November 11, 2008 4:29 PM
  • WHILE @@rowcount >0

     

    A.D.T.

    Tuesday, November 11, 2008 4:39 PM
  • Thanks for the hint to the problem ,that was the cause of the infinite loop alright.

    I re-wrote it as follows and tested, works fine now. 

    Thanks again for the help

     

    Code Snippet

    ALTER TRIGGER [dbo].[tr_CreateGenTest_i] ON [dbo].[Project] FOR INSERT

    AS

     

     

    DECLARE @rc AS INT; --Used to store the value returned by @@rowcount (# of rows affected by INSERT)

    SET @rc = @@rowcount;

    --If zero rows were affected, do nothing & exit

    IF @rc = 0 RETURN;

    SET NOCOUNT ON;

    --Declare Variables

    DECLARE @RowID AS INT,

    @GenUnitID AS VARCHAR(10),

    @ProjectID AS VARCHAR(50),

    @rcGenTest AS INT,

    @rcTests AS INT,

    @rcGenUnits AS INT,

    @TestID AS VARCHAR (5),

    @i AS INT,

    @j AS INT;

    --Populate the variable with the value of the Project ID just inserted

    SELECT @ProjectID = ProjectID FROM inserted;

    --Build a temporary table containing the different MCP's for this ProjectID

    SELECT MCP, ProjectID INTO #s

    FROM Unit WHERE ProjectID = @ProjectID

    GROUP BY MCP, ProjectID;

    --Create Temporary table with RowID

    CREATE TABLE #t (

    RowID TINYINT IDENTITY(1,1) NOT NULL,

    MCP VARCHAR (10),

    ProjectID VARCHAR (50))

    --Insert MCP and ProjID data into the Temp Table #t which will also auto-create the RowID values

    INSERT INTO #t (MCP, ProjectID)

    Select MCP, ProjectID from #s;

    -- See how many test combinations we need to create per UnitID

    SELECT @rcTests = COUNT(*) FROM GenCategories

    SELECT @rcGenUnits = COUNT (*) FROM #t

    --Each iteration of this loop, the code will grab the data from the temp table #t

    -- and build the GenTest table rows for all MCP's in the Project just added

    SET @j=1

    WHILE @j<=@rcGenUnits

    BEGIN

    --Grab the values from the next row in the Temp Table

    SELECT @GenUnitID = MCP, @ProjectID = ProjectID

    FROM #t WHERE RowID = @j

    --Increment j

    SET @j=@j+1

    --Initialise i

    SET @i=1

    --This loop INSERTs every possible test for a single unit into the GenTest table

    --Each iteration of this loop, the code will grab the data from the temp table #t

    -- and build the GenTest table rows for all MCP's in the Project just added

    WHILE @i<=@rcTests --While there are more combinations to be made

    BEGIN

    --Build a row into the UnitTest table

    SELECT @TestID = GenCatID

    FROM GenCategories WHERE RowID = @i

    INSERT INTO GenTest (GenUnitID, idGenCat, ProjectID)

    VALUES (@GenUnitID, @TestID, @ProjectID)

    -- Increment i

    SET @i=@i+1

    END

    END

    DROP TABLE #t

    DROP TABLE #s

     

     

    GO

     

     

     

     

    Tuesday, November 11, 2008 6:34 PM