+ '@' +@nameColuna +'_DE='+ @nameColuna end --- Fim query Fetch next from cColuna into @nameColuna, @tipoCampo, @tamanhoCampo -- Proxima coluna --Verifica a posicao para adicionar a ultima virgula if (@@FETCH_STATUS = 0) begin if (@tipoCampo <> 'text' and @tipoCampo <> 'ntext' and @tipoCampo <> 'image') begin set @auxDeclare = @auxDeclare + ',' set @queryInsertUpdate = @queryInsertUpdate + ',' set @queryDelete = @queryDelete + ',' set @colunaValorIU = @colunaValorIU + '+'';' set @colunaValorDE = @colunaValorDE + '+'';' end end else begin set @auxDeclare = @auxDeclare + ',@regAnterior varchar(8000), @regAtual varchar(8000), @nameTable varchar(50)' end end close cColuna deallocate cColuna set @queryInsertUpdate = @queryInsertUpdate+ ' FROM INSERTED' set @queryDelete = @queryDelete + ' FROM DELETED' --Verificando se a tabela existe. if exists (select * from sysobjects where xtype = 'u' and name = @nameTabela) begin --Criando a trigger para tabela selecionada no cursor if not exists (SELECT * FROM sysobjects WHERE name = 'tg_'+@nameTabela+'' AND type = 'tr') begin exec ('CREATE TRIGGER tg_'+ @nameTabela +' ON '+ @nameTabela +' AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; declare @usuario varchar SELECT @usuario = CAST(CONTEXT_INFO AS VARCHAR)FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID '+ @auxDeclare +' set @nameTable = '''+ @nameTabela +''' if ((exists (select * from inserted)) and (exists (select * from deleted))) begin '+ @queryDelete +' set @regAnterior = '''+ @colunaValorDE +' '+ @queryInsertUpdate +' set @regAtual = '''+ @colunaValorIU +' INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''U'', @regAnterior, @regAtual) end if ((exists (select * from inserted)) and (not exists (select * from deleted))) begin set @regAnterior = null '+ @queryInsertUpdate +' set @regAtual = '''+ @colunaValorIU +' INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''I'', @regAnterior, @regAtual) end if ((not exists (select * from inserted)) and (exists (select * from deleted))) begin '+ @queryDelete +' set @regAnterior = '''+ @colunaValorDE +' set @regAtual = null INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''D'', @regAnterior, @regAtual) END END') END ELSE BEGIN exec ('ALTER TRIGGER tg_'+ @nameTabela +' ON '+ @nameTabela +' AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; declare @usuario varchar(10) SELECT @usuario = CAST(CONTEXT_INFO AS VARCHAR)FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID '+ @auxDeclare +' set @nameTable = '''+ @nameTabela +''' if ((exists (select * from inserted)) and (exists (select * from deleted))) begin '+ @queryDelete +' set @regAnterior = '''+ @colunaValorDE +' '+ @queryInsertUpdate +' set @regAtual = '''+ @colunaValorIU +' INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''U'', @regAnterior, @regAtual) end if ((exists (select * from inserted)) and (not exists (select * from deleted))) begin set @regAnterior = null '+ @queryInsertUpdate +' set @regAtual = '''+ @colunaValorIU +' INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''I'', @regAnterior, @regAtual) end if ((not exists (select * from inserted)) and (exists (select * from deleted))) begin '+ @queryDelete +' set @regAnterior = '''+ @colunaValorDE +' set @regAtual = null INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''D'', @regAnterior, @regAtual) END END') END END FETCH NEXT FROM cTabela Into @idTabela, @nameTabela