October 18, 2008 at 12:30 pm
-- Make by Sérgio/PROINDDY/MARANHÃO/BRASIL em 15 10 2008
-- ALL CAN SHARE THIS LINE BUT DOT REMOVE THE CREDITS
You only set @nometabela end @chave (will get field for find after)
CREATE TRIGGER [TG_AUDITORIA] ON [dbo].[FUNCIONARIOS]
FOR UPDATE
AS
declare @coluna_nome varchar(60), @EXECUTA VARCHAR(1000), @NomeTabela char(30), @chave char(80)
set @nomeTabela = 'FUNCIONARIOS' -- INFORME O NOME DA TABELA
set @Chave = 'a.f_empresa+a.f_local+cast(a.f_codigo as varchar)' -- INFORME A CHAVE DE PROCURA
--CONTEUDO DA TABELA ALTERAÇÃ0
-- SELECT TRAZ TODAS AS COLUNAS DA TABELA
DECLARE GC_ALTERACAO CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @nomeTabela
OPEN GC_ALTERACAO
FETCH NEXT FROM GC_ALTERACAO
INTO @COLUNA_NOME
SELECT * INTO #INSERT2 FROM INSERTED
SELECT * INTO #DELETED2 FROM DELETED
-- LOOP PARA TODOS OS CAMPOS
WHILE (@@FETCH_STATUS = 0) BEGIN
--GRAVA ALTERAÇÃO QUE HOUVE NA COLUNA PESQUISADA EM TODOS OS REGISTROS
set @EXECUTA = 'insert into alteracao(alt_tabela,alt_chave,alt_campo,alt_valor_de,alt_valor_para) '+
'select ''' + @NomeTabela + ''','+@chave+','''+@Coluna_nome+''',B.'+@Coluna_nome+',A.'+@COLUNA_NOME+' from #insert2 a, #deleted2 b
where a.'+@Coluna_nome+' <> b.'+@Coluna_nome
exec(@executa)
FETCH NEXT FROM GC_ALTERACAO
INTO @COLUNA_NOME
END
CLOSE GC_ALTERACAO
DEALLOCATE GC_ALTERACAO
-- ESTRUTUR FOR TABLE ALTERACAO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ALTERACAO]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ALTERACAO]
GO
CREATE TABLE [dbo].[ALTERACAO] (
[ALT_CHAVE] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ALT_CAMPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ALT_VALOR_DE] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ALT_VALOR_PARA] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ALT_TABELA] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply