Trigger para auditoria

[douglasmattos]

Galera bom dia!

Estou precisando fazer uma trigger que fique monitorando uma tabela e quando um valor determinado for inserido ou atualizado eu quero que essa informação seja armazenada em outra tabela..

ex: tenho uma tabela chamada SOLICITACAO_OS onde tem os campos CD_OS, CD_USUARIO, NOME, CD_SETOR.

O que eu preciso é criar uma trigger que pegue os dados inseridos ou atualizados quando o setor for ex: 120 e insira essas informações em outra tabela...

estou iniciando em PL SQL e estou com algumas dúvidas.

valeu!

[gpereira]

Creio que código abaixo resolva teu problema.

Não compilei, pode haver erros na compilação .

Qualquer duvida posta ai.

{}'s

Selecionar tudo

---- Criando tabela de logs

CREATE TABLE TB_AUDIT_OS (
CD_OS AS VARCHAR2(150), 
CD_USUARIO  NUMBER(5), 
NOME VARCHAR2(150), 
CD_SETOR VARCHAR2(150),
USER VARCAHR2(150),
DT_ALTERACAO DATE);

-- Criando Trigger

CREATE OR REPLACE TRIGGER AUDIT_OS
  BEFORE DELETE OR INSERT OR UPDATE ON SOLICITACAO_OS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO TB_AUDIT_OS VALUES(
       :old.CD_OS, :old.CD_USUARIO, :old.NOME, :old.CD_SETOR,
     user, sysdate);
END;
/

[Pablo]

Bom dia Douglas.

Cara,,,aqui na empresa a gente tem um tal de "pega ladrão"....que é exatamente esse esquema de auditoria.
Esta tabela registra todas as "movimentações" que os usuários fazem....PRINCIPALMENTE delete....porque tem aquela ideia de "o sistema sumiu com o registro"....então esse esquema é aplicado nas principais tabelas.

A estrutura é mais ou menos assim:

você cria uma tabela de auditoria

Selecionar tudo

Create table aud_NOME_TABELA_AUDITADA( 
seq_aud  number(8),
dat_aud   date,
tip_aud   varchar2(1),
usuario_aud   varchar2(60),
TODOS OS CAMPOS DA TABELA AUDITADA
);

você cria uma SEQUENCE para inserir na coluna SEQ_AUD

Selecionar tudo

create sequence seq_aud_NOME_TABELA_AUDITADA
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;

depois você cria uma trigger para auditar isso.

Selecionar tudo

create or replace trigger aud_NOME_TABELA_AUDITADA
after insert or update or delete on NOME_TABELA_AUDITADA
for each row 
 
declare
 
   --Local variables here
 
   xtip_auditoria    varchar2(1) := 'I';
 
begin 

   --
 
   --Insere na variável a letra A para quando o registro for atualizado.
 
   if updating then
 
      xtip_auditoria := 'A';
 
      --Consistência que não permite que seja alterado os campos PK
 
      --
 
      if :new.CHAVE_PRIMARIA <> :old.CHAVE_PRIMARIA then

         raise_application_error ( -20002 , 'Você não pode alterar o(s) campo(s) " CHAVE_PRIMARIA " deste registro. Exclua e insira um registro novo.');

      end if;

   --
 
   end if;
 
   --
 
   --Insere na tabela o registro quando ele for excluído.
 
   if deleting then
 
      xtip_auditoria := 'E';
 
      --
 
      insert into aud_NOME_TABELA_AUDITADA values (
         seq_aud_NOME_TABELA_AUDITADA.nextval    ,
         sysdate                                ,
         xtip_auditoria                         ,
         user                                   ,
         :old.CADA CAMPO DA TABELA DEVE CONTER UMA LINHA DESSAS);
 
   else
 
     --
 
     --Insere na tabela o registro quando ele for alterado ou inserido.
 
      insert into aud_NOME_TABELA_AUDITADA values (
         seq_aud_NOME_TABELA_AUDITADA.nextval    ,
         sysdate                                ,
         xtip_auditoria                         ,
         user                                   ,
         :new.CADA CAMPO DA TABELA DEVE CONTER UMA LINHA DESSAS);
 
   end if;
 
--===================================================================================================================
 
end aud_NOME_TABELA_AUDITADA;

Cara...com esse esquema, aumentamos a credibilidade do nosso sistema...e evitamos MUITOS incômodos.

depois disso é fazer uma tela (somente consulta...) e deixar para o usuário consultar o que ele fez,

Espero ter ajudado.

t+

[douglasmattos]

Galera valeu mesmo pela ajuda...ajudaram muito.....

Pablo vou pegar a sua ideia e tentar modificar a minha trigger, a sua ficou bem legal...

Segue o que eu fiz...

Selecionar tudo

CREATE OR REPLACE TRIGGER TI_CHAMADOS_ITSOL_OS_LOG 
    AFTER INSERT OR UPDATE ON DBAMV.ITSOLICITACAO_OS
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW 
DECLARE
    FUNCIONARIO VARCHAR2(100);
    CURSOR cVerif IS
     SELECT 1
       FROM DBAMV.TB_SOLICITACAO_OS 
      WHERE CD_OS = :NEW.CD_OS
        AND CD_OFICINA = 6;
    v_existe NUMBER(5);
BEGIN
    OPEN  cVerif;
    FETCH cVerif INTO v_existe;
    CLOSE cVerif;
    IF (v_existe = 1) THEN
    --IF (:NEW.CD_FUNC IN (170,171,168,176,172,169,173,167,174)) THEN
    INSERT INTO DBAMV.TI_CHAMADOS_ITSOLICITACAO_OS 
         VALUES ( :NEW.CD_OS
                 ,:NEW.CD_ITSOLICITACAO_OS
                 ,:NEW.HR_INICIO
                 ,:NEW.HR_FINAL
                 ,:NEW.VL_TEMPO_GASTO_MIN
                 ,:NEW.CD_FUNC
                 ,:NEW.CD_SERVICO
                 ,:NEW.DS_SERVICO
                 ,sysdate
                 ,USER                 
                 );
    END IF;
END;

[douglasmattos]

Verdade.... na empresa onde trabalho também tem algo parecido...O pessoal fecha os chamadas e ninguém sabe quem fez, quando a gente pergunta ninguém sabe o que aconteceu...

Bom dia Douglas.

Cara,,,aqui na empresa a gente tem um tal de "pega ladrão"....que é exatamente esse esquema de auditoria.
Esta tabela registra todas as "movimentações" que os usuários fazem....PRINCIPALMENTE delete....porque tem aquela ideia de "o sistema sumiu com o registro"....então esse esquema é aplicado nas principais tabelas.

A estrutura é mais ou menos assim:

você cria uma tabela de auditoria

Selecionar tudo

Create table aud_NOME_TABELA_AUDITADA( 
seq_aud  number(8),
dat_aud   date,
tip_aud   varchar2(1),
usuario_aud   varchar2(60),
TODOS OS CAMPOS DA TABELA AUDITADA
);

você cria uma SEQUENCE para inserir na coluna SEQ_AUD

Selecionar tudo

create sequence seq_aud_NOME_TABELA_AUDITADA
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;

depois você cria uma trigger para auditar isso.

Selecionar tudo

create or replace trigger aud_NOME_TABELA_AUDITADA
after insert or update or delete on NOME_TABELA_AUDITADA
for each row 
 
declare
 
   --Local variables here
 
   xtip_auditoria    varchar2(1) := 'I';
 
begin 

   --
 
   --Insere na variável a letra A para quando o registro for atualizado.
 
   if updating then
 
      xtip_auditoria := 'A';
 
      --Consistência que não permite que seja alterado os campos PK
 
      --
 
      if :new.CHAVE_PRIMARIA <> :old.CHAVE_PRIMARIA then

         raise_application_error ( -20002 , 'Você não pode alterar o(s) campo(s) " CHAVE_PRIMARIA " deste registro. Exclua e insira um registro novo.');

      end if;

   --
 
   end if;
 
   --
 
   --Insere na tabela o registro quando ele for excluído.
 
   if deleting then
 
      xtip_auditoria := 'E';
 
      --
 
      insert into aud_NOME_TABELA_AUDITADA values (
         seq_aud_NOME_TABELA_AUDITADA.nextval    ,
         sysdate                                ,
         xtip_auditoria                         ,
         user                                   ,
         :old.CADA CAMPO DA TABELA DEVE CONTER UMA LINHA DESSAS);
 
   else
 
     --
 
     --Insere na tabela o registro quando ele for alterado ou inserido.
 
      insert into aud_NOME_TABELA_AUDITADA values (
         seq_aud_NOME_TABELA_AUDITADA.nextval    ,
         sysdate                                ,
         xtip_auditoria                         ,
         user                                   ,
         :new.CADA CAMPO DA TABELA DEVE CONTER UMA LINHA DESSAS);
 
   end if;
 
--===================================================================================================================
 
end aud_NOME_TABELA_AUDITADA;

Cara...com esse esquema, aumentamos a credibilidade do nosso sistema...e evitamos MUITOS incômodos.

depois disso é fazer uma tela (somente consulta...) e deixar para o usuário consultar o que ele fez,

Espero ter ajudado.

t+

[douglasmattos]

Fiz igual você orientou PABLO...ficou bem melhor, agora consigo pegar a ação que o usuário realizou...

valeu

[Pablo]

Salve galera...
vou dar algumas dicas (que tive problemas aqui).
1)Quando for implementar a tabela de auditoria, faça uma cópia da tabela auditada na tabela de auditoria. Ai no campo "Tipo de auditoria" você coloca como INSERT, dali em diante é que a auditoria vai começar....isso elimina aquelas questões do usuário do tipo: "AWWW,,,EU FIZ A ALTERAÇÃO ANTES DA IMPLANTAÇÃO DA AUDITORIA"..mesmo porque, você precisa "comparar" como estava antes da alteração com o que está agora...
2) se for construir uma tela para o usuário consultar, não esqueçam de bloquear a manipulação de dados...o usuário não pode alterar DE JEITO ALGUM os dados da tabela de auditoria (aquela onde está sendo salvo a cópia).
3) Cuidado que isso pode encher seu banco....afinal, se você for auditar uma tabela com 20 mil registros, você vai estar com MUITO mais registros que isso....afinal, a cada alteração será salvo 1 linha na tabela.

Abraços turma.