Why do I get "A cursor with the name already exists"?

Question

I have this trigger:

CREATE TRIGGER CHECKINGMAXQTYDAYSVACANCY
    ON TDINCI
AFTER INSERT 
AS
    DECLARE
        @incidentCode int,
        @dateStart datetime,
        @dateEnd datetime,
        @daysAccumulated int,
        @maxDaysAvailable int

    set @daysAccumulated = 0;

    select @incidentCode = CO_INCI from inserted;
    select @maxDaysAvailable = IN_DIAS_GANA from TCINCI
        where CO_INCI = @incidentCode;

    declare detailsCursor CURSOR FOR
        select FE_INIC, FE_FINA from TDINCI
        where CO_INCI = @incidentCode;

    open detailsCursor;
    
    if CURSOR_STATUS('variable', 'detailsCursor') >= 0
    begin
        fetch next from detailsCursor
            into @dateStart, @dateEnd;

        while @@FETCH_STATUS = 0
        begin
            set @daysAccumulated = @daysAccumulated + (DATEDIFF(DAY, @dateStart, @dateEnd) + 1);

            fetch next from detailsCursor
            into @dateStart, @dateEnd;
        end
        close detailsCursor;
        deallocate detailsCursor;
    end
    IF(@maxDaysAvailable > @daysAccumulated)
    BEGIN
        RAISERROR ('No se pueden ingresar mas dias de los programados en la cabecera de incidencias.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN 
    END
GO

When I do a Insert to the table TDINCI

INSERT INTO TDINCI 
VALUES (1, '20150101', '20150115', '2015-2015')

I get an error:

A cursor with the name 'detailsCursor' already exists.

I open

open detailsCursor;

and close the cursor.

close detailsCursor;
deallocate detailsCursor;

Maybe there is something with the scope of cursor that I don't manage?

Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is **not** the case. The trigger will fire **once per statement**, so if your `INSERT` statements affects 25 rows, you'll get the trigger fired **once**, but then `Inserted` will each contain 25 rows. Which of those 25 rows will your code select here?? select @incidentCode = CO_INCI from inserted;` - it's non-deterministic - one will be picked all other **ignored**. You need to rewrite your trigger to take this into account! Also: ***please avoid cursors*** - especially inside a trigger!!!!!!!! — marc_s, Feb 28 '15 at 19:51
If you still decide to use cursors, you should really check the declare cursor options, at least local, read only, forward_only / fast_forward — James Z, Feb 28 '15 at 20:05
@marc_s If I can't use cursors how would fetch all the data in inserted table: I was doing something like: `DECLARE ALLDATAINSERTED CURSOR LOCAL FOR select * from inserted;` — Erick Asto Oblitas, Feb 28 '15 at 20:19
@ErickAstoOblitas: use a **set-based** approach - use `INSERT INTO .... SELECT ....` or something like that - don't do RBAR (row-by-agonizing-row) processing - ***especially*** not in a trigger!! — marc_s, Feb 28 '15 at 20:32

score 138 · Accepted Answer · edited Jun 27 '17 at 14:38

138

You are using global cursor that will be defined each time you are calling this procedure and give you the same error.

Define a local cursor. Just put the keyword LOCAL after CURSOR:

declare detailsCursor CURSOR LOCAL FOR
...

edited Jun 27 '17 at 14:38

Kenny Evitt

9,291
5
65
93

answered Jun 09 '15 at 05:37

Ahmad

1,396
1
10
3

3

what is the difference ? – Aravin Nov 03 '20 at 09:33

Why do I get "A cursor with the name already exists"?

1 Answers1