Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Smile Unanswered: Cursor already ...

    Hi! Iīve got a little problem with the trigger bellow:

    CREATE TRIGGER GSVD_PARAMETRO
    ON TSVD_PARAMETROS
    FOR INSERT
    AS
    DECLARE @cod_dado int,@cod_grupo int, @cod_plano int, @empresa int, @contrato int,
    @termo int, @pagto char(2), @grupo int, @cota_grupo numeric, @cod_per int
    DECLARE inserts CURSOR FOR
    SELECT COD_DADO, COD_GRUPO, COD_PLANO
    FROM INSERTED
    DECLARE empresas CURSOR FOR
    SELECT T.COD_TERMO,T.TIP_PAGTO
    FROM VSVD_EMPRESA E, TSVD_CONTRATO C, TSVD_TERMO T
    WHERE C.COD_EMPRESA = E.COD_EMPRESA
    AND T.COD_CONTRATO = C.COD_CONTRATO
    AND DATEDIFF(MONTH,T.DAT_FIM_USO,GETDATE()) <= 12

    OPEN inserts

    FETCH NEXT FROM inserts INTO @cod_dado, @cod_grupo, @cod_plano

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF NOT EXISTS(
    SELECT 1
    FROM TSVD_PLANO PL
    WHERE NOT EXISTS (
    SELECT P.COD_PLANO
    FROM TSVD_PARAMETROS P
    WHERE P.COD_PLANO = PL.COD_PLANO
    AND P.COD_DADO = @cod_dado)
    )
    BEGIN

    OPEN empresas
    FETCH NEXT FROM empresas INTO @termo, @pagto
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @pagto = 'V'
    BEGIN
    SELECT DISTINCT @grupo=G.COD_GRUPO,@cota_grupo=G.QTD_COTA_GRUPO
    FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
    WHERE T.COD_TERMO = @termo
    AND P.COD_GRUPO = G.COD_GRUPO
    AND P.COD_PLANO = T.COD_PLANO
    AND P.COD_DADO = @cod_dado

    SELECT @cod_per = P.COD_PER
    FROM TSVD_PERIODO P
    WHERE P.COD_TERMO = @termo

    IF NOT EXISTS(
    SELECT 1
    FROM TSVD_SALDO S
    WHERE S.COD_GRUPO = @grupo
    AND S.COD_PER = @cod_per)
    BEGIN
    INSERT INTO TSVD_SALDO
    (COD_GRUPO,COD_PER,QTD_COTA)
    VALUES
    (@grupo,@cod_per,(@cota_grupo * 12))
    END
    END
    ELSE
    BEGIN
    SELECT DISTINCT @grupo=G.COD_GRUPO,@cota_grupo=G.QTD_COTA_GRUPO
    FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
    WHERE T.COD_TERMO = @termo
    AND P.COD_GRUPO = G.COD_GRUPO
    AND P.COD_PLANO = T.COD_PLANO
    AND P.COD_DADO = @cod_dado

    DECLARE periodos CURSOR FOR
    SELECT P.COD_PER
    FROM TSVD_PERIODO P
    WHERE P.COD_TERMO = @termo

    OPEN periodos

    FETCH NEXT FROM periodos INTO @cod_per

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF NOT EXISTS(
    SELECT 1
    FROM TSVD_SALDO S
    WHERE S.COD_GRUPO = @grupo
    AND S.COD_PER = @cod_per)
    BEGIN
    INSERT INTO TSVD_SALDO
    (COD_GRUPO,COD_PER,QTD_COTA)
    VALUES
    (@grupo,@cod_per,@cota_grupo)
    END

    FETCH NEXT FROM periodos INTO @cod_per
    END

    CLOSE periodos

    DEALLOCATE periodos

    END

    FETCH NEXT FROM empresas INTO @termo,@pagto
    END
    CLOSE empresas
    DEALLOCATE empresas
    END
    FETCH NEXT FROM inserts INTO @cod_dado, @cod_grupo, @cod_plano
    END
    CLOSE inserts
    DEALLOCATE inserts


    And itīs returning:



    Server: Msg 16915, Level 16, State 1, Procedure GSVD_PARAMETRO, Line 25
    A cursor with the name 'empresas' already exists.
    The statement has been terminated.

    I canīt figure it out! Could someone please help?

    Thank you all!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Cursor already ...

    A cursor...bad enough...In a TRIGGER?

    Wow...you're in for a world of hurt....

    If the trigger fires before another trigger firing you'll already have the cursor declared...

    Also, if it failed it will leave the cursor open...I don't see any error handling....

    I would rethink your strategy...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    How

    How about those conditions in if statement are not "YES" .. where are you closing the cursor ??? Like Brett mentioned , check errors !!

  4. #4
    Join Date
    Oct 2003
    Posts
    6

    Talking

    Sorry guys. Iīm new in sqlserver (came from oracle), and things are really different here, also Iīm developing using a wordpad (thatīs a long story...). So I donīt quite know how to handle errors within a trigger (also itīs hard to find documentation on microsoftīs library), but Iīll answer as I can:
    Itīs like this

    OPEN INSERTS CURSOR -FOR MULTI-ROWS(SO I WAS TOLD...)
    WHILE INSERTS CURSOR
    IF -SOMETHING
    DECLARE CURSOR
    OPEN CURSOR
    WHILE CURSOR
    DO STUFF
    END LOOP
    CLOSE CURSOR
    DEALLOCATE CURSOR - HEREīS WHERE IT SHOULD PREVENT
    THE TRIGGER FINDING THE CURSOR
    DECLARED IN THE NEXT TIME
    (OR SO I WAS TOLD...)
    END IF
    END LOOP

    Hope that helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •