"cursor does not exist" error in trigger

Question

"cursor does not exist" error in trigger

Aaron Olmstead

SSC Eights!

Points: 805
More actions
September 4, 2004 at 3:52 pm

#60421

I have a trigger on a "expense report" table that fires on inserts & updates. Basically the trigger does 2 things: 1) if an expense is of "type" visa, it validates that the "merchant" field is valid, and 2) if the "expense description" is personal car mileage (miles > 0), it automatically calculates the expense amount using a user-defined function; also, I use a cursor on the inserted table so that the trigger can handle multi-row inserts/updates. The trigger compiles fine, however, when I try to update a row in table, I always get the error: "Another user has modified the contents of this table...A cursor with the name "curInserted" does not exist..." Any ideas on what the problem could be (trigger code below).
Also, really dumb question: How do I open the debugger? I see it referenced in online books, but for the life of me I cannot figure out how I am supposed to get into debugger mode...
CREATE TRIGGER trgExpenseIU ON dbo.tblExpenses
FOR INSERT, UPDATE
AS
DECLARE
@id int,
@miles int,
@date datetime,
@type nvarchar(50),
@merchant nvarchar(50)
DECLARE curInserted CURSOR
FOR SELECT Expense_PK, TransDate, Miles, Type, Merchant FROM Inserted
OPEN curInserted
FETCH NEXT FROM curInserted
INTO @id, @date, @miles, @type, @merchant
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@type = 'Visa' and @merchant is null)
BEGIN
    RAISERROR ('Merchant must be specified for Visa Expenses', 16, 1)
    ROLLBACK TRANSACTION
END
ELSE
    IF @miles > 0
    BEGIN
      UPDATE dbo.tblExpenses
      SET Amount = dbo.fncMileageRate(@date) * Miles,
      ExpDesc_FK = 'Personal Car Mileage',
      Type = 'Cash'
      WHERE Expense_PK = @id
    END
FETCH NEXT FROM curInserted
INTO @id, @date, @miles, @type, @merchant
END
CLOSE curInserted
DEALLOCATE curInsterted

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 1

Your First Problem is that you did the spelling mistake in deallocating the cursor curInsterted instead of curInserted.

To debug your trigger ,first install the SQL server debugger and then in query analyzer execute the system strored procedure sp_sdidebug with parameter 'legacy_on'. now open object browser with f8 key and right click on the procedure and select the last option debug. thats 'it now you start debugging.

But, you will come to know that there is no option to debug the triggers and user defined function here works a trick...

create one temporary procedure for debugging purpose only . i generally create procedure named AAA because in object browser it comes first to select, in order.

now in strored procedure write the code which invokes you sp/udfn or trigger for example

Alter Procedure AAA

AS

update table set F=F+1

Now debug the procedue AAA which will take you to the trigger which is for update

So happy debugging,

Rohit