CREATE LOG TABLE AND TRIGGER

• hsergios

  SSC Rookie

  Points: 46

  More actions

  October 18, 2008 at 12:30 pm

  #214145

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