Invalid cursor state

Question

We have triggers on a table called OSPP to save specific data to a table for later use. I get the following error in SAP when adding more than one line at a time to the table.

Invalid Cursor State

We have SQL Server 2005 SP3 (but I tried it on a clean 2005 install, on SP1 and SP2)

The one trigger :

CREATE TRIGGER [dbo].[tr_OSPP_Insert]
   ON  [dbo].[OSPP]
   FOR INSERT
AS 
BEGIN
    Declare @ItemCode varchar(255)
    Declare @CardCode varchar(255)
    Declare @Price decimal(18,2)
    Declare @ListNum bigint 
    Declare @ID bigint
    Declare @Remote char(1)
 
 
    DECLARE db_cursor CURSOR FOR 
    SELECT ItemCode, CardCode, Price, ListNum
    FROM INSERTED
 
    OPEN db_cursor  
    FETCH NEXT
    FROM db_cursor  INTO @ItemCode, @CardCode, @Price, @ListNum
    WHILE @@FETCH_STATUS = 0
    BEGIN
 
    SELECT @Remote = isnull(U_Remote, 'N') FROM OITM WHERE ItemCode = @ItemCode
 
        IF ltrim(rtrim(upper(@Remote))) = 'Y'
        BEGIN
 
        SELECT @ID = U_ID FROM [dbo].[@BDS_MAINTENANCE]
        UPDATE [dbo].[@BDS_MAINTENANCE] set U_ID = U_ID + 1
 
        INSERT INTO [dbo].[@BDS_REMOTESPECIALPRICELIST]
        (   
            Code,
            [Name],
            U_ID,
            U_ItemCode,
            U_CardCode,
            U_Price,
            U_ListNum,
            U_TransactionType,
            U_Uploaded
        ) VALUES (
            @ID,
            '_' + cast(@ID as VARCHAR(50)),
            @ID,
            @ItemCode,
            @CardCode,
            @Price,
            @ListNum,
            1,
            0
        )
 
 
    FETCH NEXT
    FROM db_cursor INTO @ItemCode, @CardCode, @Price, @ListNum
    END
 
    CLOSE db_cursor  
    DEALLOCATE db_cursor
    
 
END
 
END

We also tried :

CREATE TRIGGER [dbo].[tr_OSPP_Insert]
   ON  [dbo].[OSPP]
   FOR INSERT
AS 
BEGIN
 
    SELECT * INTO [@TEMPTABLE222] FROM INSERTED 
 
END

But still get the same error.

Do you guys have any idea what is wrong?

Thanks in advance!

Answer 1

I count three Begins, and three Ends. But it's the second pair that represent the cursor loop - so I'd move your Close/Deallocate to be after the second End, rather than before. E.g:

    FETCH NEXT
    FROM db_cursor INTO @ItemCode, @CardCode, @Price, @ListNum
    END

    CLOSE db_cursor  
    DEALLOCATE db_cursor
END

Probably needs to be:

    END
    FETCH NEXT
    FROM db_cursor INTO @ItemCode, @CardCode, @Price, @ListNum

END
CLOSE db_cursor  
DEALLOCATE db_cursor

(I've also moved the fetch next one level out, since otherwise you only move the cursor forwards inside your IF condition)

---

And one style comment (can't resist). It's generally considered good practice to SET NOCOUNT ON within the body of a trigger, to avoid sending lots of extra n rows affected messages.