CREATE LOG TABLE AND TRIGGER

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Srinivasulu » CREATE LOG TABLE AND TRIGGER

CREATE LOG TABLE AND TRIGGER

Rate Topic

Display Mode

Topic Options

Author

Message

hsergios

Posted Saturday, October 18, 2008 12:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 05, 2011 3:28 PM
Points: 4, Visits: 16

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

Post #588148

« Prev Topic | Next Topic »

Permissions