Invalid cursor state

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!

Solution

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.