SQL Question
'cursor_name' does not exist
I wrote following trigger for audit purposes. When I try to update a record in
Sales.SalesOrderHeader
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.