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? Thanks in advance.

Answer 1

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 ...