none
Erro na Inserção de mais de um valor dentro de um cursor no Sql Server RRS feed

  • Pergunta

  • Bom Dia, estou com o seguinte problema, criei uma trigger que é disparada apos um insert ou update em uma tabela.

    quando faço apena um insert de um único registro a trigger funciona perfeitamente, o problema é quando e mais de um insert 

    insert into 
    acao_coluna([cod],   [cod_acao],[coluna],[old],[new])
          values (53060292, 53060291,  'cod','538403','538403'),
                    (53060293, 53060291, 'cod_uip','5227614','5227614'),
                    (53060294, 53060291, 'fabr_lum', '18','18'),
                    (53060295, 53060291, 'tipo_lum', '3','22')

    ALTER TRIGGER  [dbo].[trigger_coluna_traducao_alias] ON  [dbo].[acao_coluna]
     AFTER INSERT,UPDATE
     AS 
       BEGIN  
     DECLARE @tabela  AS VARCHAR(MAX)
     DECLARE @coluna AS VARCHAR (MAX)
     DECLARE @sql AS VARCHAR (MAX)
     DECLARE @sql2 AS NVARCHAR (MAX)
     DECLARE @old AS VARCHAR (MAX)
     DECLARE @new AS VARCHAR (MAX)
     DECLARE @result AS VARCHAR (MAX)
     DECLARE @cod AS INT

     SET @tabela = (SELECT tabela FROM acao WHERE[cod] = (SELECT cod_acao FROM inserted))
          --select @tabela

     SELECT @coluna = coluna , @old = old, @new = new, @cod = cod FROM Inserted
          --select  @coluna

     SET @sql = (SELECT SQL FROM traducao_campo tc,traducao_tabela tt WHERE tc.campo_tabela = @coluna AND tt.nome_tabelas =@tabela  and tc.id_traducao_tabela = tt.id )
     select  @sql

          IF( @new IS NOT NULL)
            BEGIN
            WHILE @@FETCH_STATUS = 0 -- pega o conteudo total da lista
              BEGIN
               SET @sql2 = REPLACE (@sql,'[COD]', @new)
               --select @sql2 
               SET @sql2 = ('Declare  result_cursor2 CURSOR FOR '+@sql2)  
                    --select @sql2 

               EXEC sp_executesql @sql2 

               OPEN result_cursor2
                    FETCH NEXT FROM result_cursor2
                    INTO @result

           SELECT @result

               CLOSE result_cursor2
                    DEALLOCATE result_cursor2

               UPDATE acao_coluna  SET new_desc = @result  WHERE cod = @cod 
              END

           END

    /** Insert no campo old na tabela acao_coluna  **/
      IF( @old IS NOT NULL)
     BEGIN

     SET @sql2 = REPLACE (@sql,'[COD]', @old)
      --select @sql2 

     SET @sql2 = ('Declare  result_cursor2 CURSOR FOR '+@sql2)  
          select @sql2 

     EXEC sp_executesql @sql2 


     OPEN result_cursor2
          FETCH NEXT FROM result_cursor2
          INTO @result


     select @result

     CLOSE result_cursor2
          DEALLOCATE result_cursor2

     UPDATE acao_coluna  SET old_desc = @result  WHERE cod = @cod 



    END

    END

    terça-feira, 22 de agosto de 2017 14:53

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 22 de agosto de 2017 15:01
  • Compartilho a sugestão do José, e alem disso está disponibilizado um mini-curso de um cara muito bom

    da um olhada na trilha,

    Evitando Erros Comuns na Elaboração de Código T-SQL

    um conselho , não implemente isso em produção antes de ver o artigo do José e ver esses videos. 

    Wesley Neves - Brasilia-DF

     
    wesley.si.neves@gmail.com
    MTA-SQL Server
    MTA- Web Development
    Analista Desenvolvedor.NET
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"





    Wesley Neves

    terça-feira, 22 de agosto de 2017 15:19
  • Sugestão inicial: Armadilhas na programação de procedimentos de gatilho.

    Poderia nos informar o que o procedimento de gatilho deve fazer sempre que ocorrer INSERT ou UPDATE na tabela acao_coluna?


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    Bom José Diz, os campos da tabelas acao_coluna([old], [new])  guarda estados de um objeto, quando é inserido um registro novo o campo new é preenchido, e quando é atualizado o campo old recebe new e new o novo  registro.

    isso possibilita realizar um rollback nas informações do objeto em questão.

    Sou completamente contra utilização de trigger. porem nem sempre as decisões é escolha nossa.    

     o procedimento ja funciona  quando é único insert, 

    insert into 
    acao_coluna([cod],   [cod_acao],[coluna],         [old],                [new])
        values (53060292, 53060291,  'cod',           '538402',             '538403')
    terça-feira, 22 de agosto de 2017 17:51
  • Deleted
    terça-feira, 22 de agosto de 2017 17:53
  • (...) os campos da tabelas acao_coluna([old], [new])  guarda estados de um objeto, quando é inserido um registro novo o campo new é preenchido, e quando é atualizado o campo old recebe new e new o novo  registro.
    isso possibilita realizar um rollback nas informações do objeto em questão.

    E qual é a relação com as tabelas traducao_campo e traducao_tabela?

    O que compreendi da análise do código é que na tabela acao_coluna, cod_acao representa o código de uma ação (na tabela acao) que indica em qual tabela foi realizada a alteração; é isto?

    No exemplo inicial, das 4 linhas inseridas somente uma delas contém alteração de valor sendo que as demais os valores permanecem os mesmos. O que fazer nesses casos em que, na prática, o valor da coluna não é alterado?

    O INSERT do exemplo inicial é gerado automaticamente por procedimento de gatilho associado à tabela em que estão as colunas que foram alteradas?

    No trecho
         SELECT SQL FROM traducao_campo tc,traducao_tabela tt 
    a coluna SQL é de qual tabela?

    Me parece que os trechos
         SET @sql2 = REPLACE (@sql,'[COD]', @new)
    e
         SET @sql2 = REPLACE (@sql,'[COD]', @old)
    não fazem nada, pois não existe "[COD]" em @SQL.

    Na tabela acao_coluna a coluna cod é chave primária?

    --

    Eis esboço do código:

    -- código #1
    UPDATE AC
      set new_desc= ??,
           old_desc= ??
      from INSERTED as I
           inner join acao as A on A.cod = I.cod_acao
           inner join acao_coluna as AC on AC.cod = I.cod
           inner join traducao_tabela as TT on TT.nome_tabelas = A.tabela
           inner join traducao_campo as TC on TC.campo_tabela = I.coluna
                                              and TC.id_traducao_tabela = TT.id
      where I.old <> I.new;


    e-mail       José Diz     Belo Horizonte, MG - Brasil


    Bom eu tenho uma tabela chamada acao_coluna e quando dados são inserido nela dispara esta trigger

    neste banco tenho mais duas tabelas traducao_tabela e traducao_campo, essas duas tem campos com valores de traducao para serem referenciado na acao_coluna.

    O que compreendi da análise do código é que na tabela acao_coluna, cod_acao representa o código de uma ação (na tabela acao) que indica em qual tabela foi realizada a alteração; é isto? 

    R: sim isso mesmo 

    No trecho 
         SELECT SQL FROM traducao_campo tc,traducao_tabela tt  
    a coluna SQL é de qual tabela? 

    R: É da tabela traducao_campo , o campo tipo varchar com esse valor  'Select ''[COD]'' as Result', quando o valor da coluna for um valor absoluto ele recebe esse campo, int, varchar, ou char,

    se o dado for date, ele tem esse valor : 'select Convert(varchar,convert(datetime,''[COD]''),103)  as result'

    Me parece que os trechos
         SET @sql2 = REPLACE (@sql,'[COD]', @new)
    e
         SET @sql2 = REPLACE (@sql,'[COD]', @old)
    não fazem nada, pois não existe "[COD]" em @SQL.

    R: na verdade concateno tudo ja [cod] é passado como parâmetro

    Na tabela acao_coluna a coluna cod é chave primária?

    R Sim cod é a chave primaria e cod_acao é a fk, na verdade cada linha inserida no insert referencia uma tabela, e a fk é a referencia do  mudança do objeto, no insert acima passo 8 valores, então são 8 tabelas que recebera as alterações.


    • Editado Robert Sancts terça-feira, 22 de agosto de 2017 19:36 melhorar a explicação
    terça-feira, 22 de agosto de 2017 19:34
  • Deleted
    terça-feira, 22 de agosto de 2017 20:35
  • Deleted
    • Marcado como Resposta Robert Sancts quarta-feira, 23 de agosto de 2017 13:44
    terça-feira, 22 de agosto de 2017 21:32
  • Uma solução é fazer com que o cursor aponte para a tabela INSERTED e então tratar linha a linha. Eis o esboço do código:

    -- código #2
    ALTER TRIGGER  [dbo].[trigger_coluna_traducao_alias] 
       on [dbo].[acao_coluna]
       after INSERT as
    begin
    declare cursor linhaINSERT 
         forward_only read_only
         for SELECT [cod], [cod_acao], [coluna], [old], [new]
               from INSERTED 
               order by [cod];
    
    declare @cod int, @cod_acao int, @coluna varchar(2000), @old varchar(2000), @new varchar(2000);
    declare @nomeTabela varchar(200), @convColuna varchar(2000), @comandoSQL nvarchar(2000);
    
    --
    OPEN linhaINSERT;
    
    -- lê primeira linha
    FETCH NEXT from linhaINSERT
         into @cod, @cod_acao, @coluna, @old, @new;
    
    while @@fetch_status = 0
      begin
      -- obtém nome da tabela
      set @nomeTabela= (SELECT tabela 
                         from acao
                         where cod = @cod_acao);
    
      -- obtém código SQL de conversão do conteúdo da coluna
      set @convColuna= (SELECT TC.SQL 
                          from traducao_campo as TC 
                               inner join traducao_tabela as TT on TC.id_traducao_tabela = TT.id 
                          where TC.campo_tabela = @coluna 
                                and TT.nome_tabelas = @nomeTabela);
    
      -- 
      IF @new is not null
        begin
        ...
        end;
    
      -- 
      IF @old is not null
        begin
        ...
        end;
    
      -- lê próxima linha
      FETCH NEXT from linhaINSERT
           into @cod, @cod_acao, @coluna, @old, @new;
      end;
    
    CLOSE linhaINSERT;
    DEALLOCATE linhaINSERT;
    end;
    go
    Observe que ainda não há a parte de atualização, pois não compreendi totalmente o contexto. O fato de ter que executar um comando SQL dinâmico me parece muito estranho.


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    José Diz muito obrigado. só troquei a ordem do cursor que estava ao contrario. porem a sua solução já me atende. o meu muitíssimo obrigado.

       
    quarta-feira, 23 de agosto de 2017 13:47
  • Deleted
    quarta-feira, 23 de agosto de 2017 22:32