'cursor_name' does not exist

Question

I wrote following trigger for audit purposes. When I try to update a record in

Sales.SalesOrderHeader

table I get following error:

Msg 16916, Level 16, State 1, Procedure tr_AuditUpdate, Line 11 A
cursor with the name 'c_Audit2' does not exist.

This is the trigger:

 USE [AdventureWorks2008R2]

    GO

    /****** Object:  Trigger [Sales].[tr_AuditUpdate]    Script Date: 9/19/2016 9:27:36 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER trigger [Sales].[tr_AuditUpdate]

    on [Sales].[SalesOrderHeader]

    for Update

    AS

    SET NOCOUNT ON

    BEGIN

    DECLARE @EntityCode1 int

    DECLARE @OldStatus1 nvarchar(100)

    DECLARE @NewStatus1 nvarchar(100)



    if @@ROWCOUNT>0

        DECLARE c_Audit2 CURSOR LOCAL FOR

        Select  d.SalesOrderID,d.Status,i.Status

        From    deleted AS d

        join inserted AS i

        on i.SalesOrderID=d.SalesOrderID

    Open c_Audit2





    Fetch Next From c_Audit2

    INTO @EntityCode1, @OldStatus1, @NewStatus1



    While @@FETCH_STATUS=0

    BEGIN

      execute sp_Audit @AuditedTable='Sales.SalesOrderHeader',

                       @EntityCode=@EntityCode1,

                       @OldStatus=@OldStatus1,

                       @NewStatus1=@NewStatus1,

                       @AuditUpdate='+',

                       @AuditInsert='-',

                       @AuditDelete='-'



      Fetch Next From c_Audit2

      INTO @EntityCode1, @OldStatus1, @NewStatus1

    END



    CLOSE c_Audit2

    DEALLOCATE c_Audit2

    END

This is the update statement

update Sales.SalesOrderHeader

set Status=4

where SalesOrderID=43659

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

the cursor will only be created if @@ROWCOUNT>0 as you have written in the beginning of the trigger. Remove that if and it will work fine.

if @@ROWCOUNT>0
    DECLARE c_Audit2 CURSOR LOCAL FOR
    Select  d.SalesOrderID,d.Status,i.Status
    From    deleted AS d
    join inserted AS i
    on i.SalesOrderID=d.SalesOrderID
open c_Audit2 -- fails when @@rowcount=0

because of the if the cursor will not always be created.

then your next statement is

open c_Audit2

and that will fail if "@@ROWCOUNT>0" returns false

remove this code : if @@ROWCOUNT>0 and your trigger will work.

'cursor_name' does not exist

Sign up