Usuário com melhor resposta
Erro na Inserção de mais de um valor dentro de um cursor no Sql Server

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
Respostas
-
Deleted
- Marcado como Resposta Robert Sancts quarta-feira, 23 de agosto de 2017 13:44
Todas as Respostas
-
-
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
-
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') -
-
(...) 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.
- Editado Robert Sancts terça-feira, 22 de agosto de 2017 19:36 melhorar a explicação
-
-
Deleted
- Marcado como Resposta Robert Sancts quarta-feira, 23 de agosto de 2017 13:44
-
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.
-