Son Kur Son Kur - 2 months ago 8
SQL Question

'cursor_name' does not exist

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

Answer

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.