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

Question

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

Answer 1

Deleted

Answer 2

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

 
[e-mail]
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

Answer 3

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?

---

      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')

Answer 4

Deleted

Answer 5

(...) 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;

---

      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.

Answer 6

Deleted

Answer 7

Deleted

Answer 8

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.

---

      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.

   

Answer 9

Deleted