sql – 为什么我得到“游标的名称已经存在”？

发布时间：2020-12-12 08:23:40 所属栏目：MsSql教程来源：网络整理

导读：我有这个触发器 CREATE TRIGGER CHECKINGMAXQTYDAYSVACANCY ON TDINCIAFTER INSERT AS DECLARE @incidentCode int,@dateStart datetime,@dateEnd datetime,@daysAccumulated int,@maxDaysAvailable int set @daysAccumulated = 0; select @incidentCode = CO_

我有这个触发器

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

当我做一个插入表TDINCI

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

我收到一个错误：

A cursor with the name ‘detailsCursor’ already exists.

我打开

open detailsCursor;

并关闭光标.

close detailsCursor;
deallocate detailsCursor;

也许有一些我没有管理的游标的范围？提前致谢.

解决方法

您正在使用全局游标,每当您调用此过程并给出相同的错误时,它将被定义. 定义本地游标.只需在CURSOR后面放置关键字LOCAL,如下所示：申报详细信息CURSOR LOCAL FOR

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!