Sim, você não é o primeiro a desejar fazer uma trigger genérica de LOG.
Infelizmente, o que você deseja, não é possível no Oracle. Veja só: Variáveis :NEW e :OLD são como variáveis binds! Só valem dentro das triggers e você não pode acessar dentro de funções essas variáveis dinamicamente.
que você deseja! (ou seja, algo que "monte" o código da trigger pra você! (Usando o dicionário de dados, ALL_COLUMNS, ALL_TABLES, etc)
Este caso abaixo não é exatamente o seu caso, mas você pode se basar nele pra ter uma idéia. (essa trigger não é genérica, tipo, funciona apenas pra essa tabela.
No seu caso, você teria que colocar o NOME DA TABELA, e ignorar esses campos "embarque" que tem nessa tabela.
Selecionar tudo
CREATE OR REPLACE TRIGGER TG_TB_EMBARQUE_LOG_AIU
AFTER INSERT OR UPDATE
ON TB_EMBARQUE
FOR EACH ROW
-- Monta o histórico de alteração
DECLARE
--VARIAVEIS DA TRIGGER
VSEQ number;
procedure BUSCA_SEQ
is
begin
select SQ_TB_LOG_EMBARQUE.NEXTVAL
INTO VSEQ
FROM DUAL;
end;
procedure HIST (
VOLD varchar2,
VNEW varchar2,
VCAMPO varchar2,
VCAMPO_DSC varchar2,
VDOMAIN varchar2 DEFAULT NULL
) is
VDOLD varchar2(100);
VDNEW varchar2(100);
begin
--Se alterou um campo
if VOLD <> VNEW
or ( VOLD is null and VNEW is not null )
or ( VOLD is not null and VNEW is null ) then
--Se é um campo da CG_REF_CODES, busca descrição.
if vdomain is not null
then
begin
select RV_MEANING
into VDOLD
from CG_REF_CODES
where RV_DOMAIN =VDOMAIN
and RV_LOW_VALUE=VOLD;
EXCEPTION WHEN OTHERS THEN NULL;
end;
begin
select RV_MEANING
into VDNEW
from CG_REF_CODES
where RV_DOMAIN =VDOMAIN
and RV_LOW_VALUE=VNEW;
EXCEPTION WHEN OTHERS THEN NULL;
end;
end if;
BUSCA_SEQ;
--Insere um novo histórico.
insert into TB_LOG_EMBARQUE
( SQ_LOG
, CD_SEGURADO
, NU_mês_EMBARQUE
, NU_ANO_EMBARQUE
, CD_EMBARQUE
, DT_LOG
--
, NM_CAMPO
, DS_CAMPO
, DS_CONTEUDO_ORIGINAL
, DS_CONTEUDO_NOVO
, NM_USUARIO
, TP_OPERACAO
)
values
( VSEQ
, :NEW.CD_SEGURADO
, :NEW.NU_mês_EMBARQUE
, :NEW.NU_ANO_EMBARQUE
, :NEW.CD_EMBARQUE
, SYSDATE
--
, SUBSTR(VCAMPO,1,30)
, nvl(SUBSTR(VCAMPO_DSC,1,30) , SUBSTR(VCAMPO,1,30)) --se não tem descr, coloca o nome do campo!
, nvl(vdold, VOLD) --se tem dominio, usa
, nvl(vdnew, VNEW) --se tem dominio, usa
, USER
, 'A'
);
end if;
end;
-- ********************************
-- **** AQUI COMEÇA A TRIGGER ****
-- ********************************
begin
---------------------------------------------------
-- I N S E R T
---------------------------------------------------
if INSERTING then
BUSCA_SEQ;
insert into TB_LOG_EMBARQUE
( SQ_LOG
, CD_SEGURADO
, NU_mês_EMBARQUE
, NU_ANO_EMBARQUE
, CD_EMBARQUE
, DT_LOG
--
, NM_CAMPO
, DS_CAMPO
, DS_CONTEUDO_ORIGINAL
, DS_CONTEUDO_NOVO
, NM_USUARIO
, TP_OPERACAO
)
values
( VSEQ
, :NEW.CD_SEGURADO
, :NEW.NU_mês_EMBARQUE
, :NEW.NU_ANO_EMBARQUE
, :NEW.CD_EMBARQUE
, SYSDATE
--
, NULL
, NULL
, NULL
, NULL
, USER
, 'I'
);
---------------------------------------------------
-- U P D A T E
---------------------------------------------------
elsif UPDATING then
--Testa campo por campo.
hist( :old.CD_SEGURADO ,:new.CD_SEGURADO ,'CD_SEGURADO' ,'Cód Segurado');
hist( :old.NU_mês_EMBARQUE ,:new.NU_mês_EMBARQUE ,'NU_mês_EMBARQUE' ,'Mês do Embarque');
hist( :old.NU_ANO_EMBARQUE ,:new.NU_ANO_EMBARQUE ,'NU_ANO_EMBARQUE' ,'Ano do Embarque');
hist( :old.CD_EMBARQUE ,:new.CD_EMBARQUE ,'CD_EMBARQUE' ,'Código Seq do embarque');
hist( :old.NU_DOC_INI ,:new.NU_DOC_INI ,'NU_DOC_INI' ,'Número do Doc Inicial');
hist( :old.NU_DOC_FIM ,:new.NU_DOC_FIM ,'NU_DOC_FIM' ,'Número do Doc Final');
hist( :old.SQ_DOC ,:new.SQ_DOC ,'SQ_DOC' ,'Seq do Documento');
hist( :old.DS_PLACA_EMBARQUE ,:new.DS_PLACA_EMBARQUE ,'DS_PLACA_EMBARQUE' ,'Placa');
--hist( :old.VL_IS_EMBARQUE ,:new.VL_IS_EMBARQUE ,'VL_IS_EMBARQUE' ,'I.S. do Embarque');
-- no IS EMBARQUE fica a SOMA dos 2 campos. Por isso, pro usuário não se confundir, a gente mostra direitinho no LOG.
hist( nvl(:old.VL_IS_EMBARQUE,0) - nvl(:old.VL_IS_CONTAINER,0)
, nvl(:new.VL_IS_EMBARQUE,0) - nvl(:new.VL_IS_CONTAINER,0) ,'VL_IS_EMBARQUE' ,'I.S. do Embarque');
hist( :old.DS_UF_ORIGEM ,:new.DS_UF_ORIGEM ,'DS_UF_ORIGEM' ,'UF Origem');
hist( :old.CD_CIDADE_ORIGEM ,:new.CD_CIDADE_ORIGEM ,'CD_CIDADE_ORIGEM' ,'Cód Cidade Origem');
hist( :old.DS_UF_DESTINO ,:new.DS_UF_DESTINO ,'DS_UF_DESTINO' ,'UF Destino');
hist( :old.CD_CIDADE_DESTINO ,:new.CD_CIDADE_DESTINO ,'CD_CIDADE_DESTINO' ,'Cód Cidade Destino');
hist( :old.CD_PAIS_ORIGEM ,:new.CD_PAIS_ORIGEM ,'CD_PAIS_ORIGEM' ,'Cód do País Origem');
hist( :old.CD_PAIS_DESTINO ,:new.CD_PAIS_DESTINO ,'CD_PAIS_DESTINO' ,'Cód do País Destino');
hist( :old.CD_LOTE ,:new.CD_LOTE ,'CD_LOTE' ,'Lote');
hist( :old.FL_URBANO_SUBURBANO ,:new.FL_URBANO_SUBURBANO ,'FL_URBANO_SUBURBANO' ,'U/S');
hist( :old.FL_TIPO_MERCADORIA ,:new.FL_TIPO_MERCADORIA ,'FL_TIPO_MERCADORIA' ,'Tipo Mercadoria');
hist( :old.CD_PROCESSO ,:new.CD_PROCESSO ,'CD_PROCESSO' ,'Código do Processo');
hist( :old.SQ_PROCESSO ,:new.SQ_PROCESSO ,'SQ_PROCESSO' ,'Seq do Processo');
hist( :old.NU_ANO_PROCESSO ,:new.NU_ANO_PROCESSO ,'NU_ANO_PROCESSO' ,'Ano do Processo');
hist( :old.CD_TIPO_EMBARQUE ,:new.CD_TIPO_EMBARQUE ,'CD_TIPO_EMBARQUE' ,'Tp Importação');
hist( :old.NU_CPF_MOTORISTA ,:new.NU_CPF_MOTORISTA ,'NU_CPF_MOTORISTA' ,'CPF Motorista');
hist( :old.FL_TIPO_EMBARQUE ,:new.FL_TIPO_EMBARQUE ,'FL_TIPO_EMBARQUE' ,'Tipo do Embarque', 'FL_TIPO_EMBARQUE');
hist( :old.NU_CAIXA_POSTAL ,:new.NU_CAIXA_POSTAL ,'NU_CAIXA_POSTAL' ,'Caixa Postal');
hist( :old.NM_ARQUIVO ,:new.NM_ARQUIVO ,'NM_ARQUIVO' ,'Arquivo');
hist( :old.CD_COLETOR ,:new.CD_COLETOR ,'CD_COLETOR' ,'Coletor');
hist( :old.CD_RAMO ,:new.CD_RAMO ,'CD_RAMO' ,'Cód Ramo');
hist( :old.CD_SUB_RAMO ,:new.CD_SUB_RAMO ,'CD_SUB_RAMO' ,'Cód Sub_ramo');
hist( :old.SQ_COLETOR ,:new.SQ_COLETOR ,'SQ_COLETOR' ,'Seq Coletor');
hist( :old.CD_SERIE ,:new.CD_SERIE ,'CD_SERIE' ,'Série');
hist( :old.CD_SUB_SERIE ,:new.CD_SUB_SERIE ,'CD_SUB_SERIE' ,'Sub-Série');
hist( :old.VL_IS_CONTAINER ,:new.VL_IS_CONTAINER ,'VL_IS_CONTAINER' ,'I.S. Container');
hist( :old.FL_VEICULO_NOVO ,:new.FL_VEICULO_NOVO ,'FL_VEICULO_NOVO' ,'Veículo Novo');
hist( :old.FL_RODO_FLUVIAL ,:new.FL_RODO_FLUVIAL ,'FL_RODO_FLUVIAL' ,'Fluvial');
hist( :old.FL_MERCADORIA_NOVA ,:new.FL_MERCADORIA_NOVA ,'FL_MERCADORIA_NOVA' ,'Mercad Nova');
hist( :old.CD_REBOCADOR ,:new.CD_REBOCADOR ,'CD_REBOCADOR' ,'Rebocador');
hist( :old.CD_BALSA ,:new.CD_BALSA ,'CD_BALSA' ,'Balsa');
hist( :old.CD_COBERTURA ,:new.CD_COBERTURA ,'CD_COBERTURA' ,'Cobertura');
hist( :old.CD_TIPO_MERCADORIA ,:new.CD_TIPO_MERCADORIA ,'CD_TIPO_MERCADORIA' ,'Tipo Mercad');
hist( :old.FL_VEICULO_PROPRIO ,:new.FL_VEICULO_PROPRIO ,'FL_VEICULO_PROPRIO' ,'Veíc Próprio');
hist( TO_CHAR(:old.DT_SAIDA_EMBARQUE ,'DD/MM/YYYY-HH24:MI'), TO_CHAR(:new.DT_SAIDA_EMBARQUE ,'DD/MM/YYYY-HH24:MI') ,'DT_SAIDA_EMBARQUE' , 'Data Saída');
hist( TO_CHAR(:old.DT_DIGITACAO ,'DD/MM/YYYY-HH24:MI'), TO_CHAR(:new.DT_DIGITACAO ,'DD/MM/YYYY-HH24:MI') ,'DT_DIGITACAO' , 'Data Digit. Coletor');
hist( TO_CHAR(:old.DT_INICIO_VIGENCIA,'DD/MM/YYYY-HH24:MI'), TO_CHAR(:new.DT_INICIO_VIGENCIA,'DD/MM/YYYY-HH24:MI') ,'DT_INICIO_VIGENCIA', 'Data Início Vigência');
hist( TO_CHAR(:old.DT_INICIO ,'DD/MM/YYYY-HH24:MI'), TO_CHAR(:new.DT_INICIO ,'DD/MM/YYYY-HH24:MI') ,'DT_INICIO' , 'Data Início');
hist( TO_CHAR(:old.DT_FIM ,'DD/MM/YYYY-HH24:MI'), TO_CHAR(:new.DT_FIM ,'DD/MM/YYYY-HH24:MI') ,'DT_FIM' , 'Data Fim');
---------------------------------------------------
-- D E L E T E
---------------------------------------------------
elsif DELETING then
BUSCA_SEQ;
insert into TB_LOG_EMBARQUE
( SQ_LOG
, CD_SEGURADO
, NU_mês_EMBARQUE
, NU_ANO_EMBARQUE
, CD_EMBARQUE
, DT_LOG
--
, NM_CAMPO
, DS_CAMPO
, DS_CONTEUDO_ORIGINAL
, DS_CONTEUDO_NOVO
, NM_USUARIO
, TP_OPERACAO
)
values
( VSEQ
, :old.CD_SEGURADO
, :old.NU_mês_EMBARQUE
, :old.NU_ANO_EMBARQUE
, :old.CD_EMBARQUE
, SYSDATE
--
, NULL
, NULL
, NULL
, NULL
, USER
, 'E'
);
end if;
exception when others
then dbms_output.put_line('Erro: Trigger TG_TB_EMBARQUE_LOG_AUI - '||SQLERRM);
raise_application_error (-20000, 'Erro: Trigger TG_TB_EMBARQUE_LOG_AUI - '||SQLERRM);
end; --fim da trigger
Eu usei um script pra gerar o código dos UPDATE, que tem que ser único pra cada campo:
Assim, eu consigo logar INSERT, UPDATE e DELETE e fica muito bem feito, pois ele poe até as descrições que estão na CG_REF_CODES.
Selecionar tudo
create table audit_tbl
( timestamp date,
who varchar2(30),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000)
)
/
create or replace package audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 );
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date );
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number );
end;
/
create or replace package body audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;
end audit_pkg;
/
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql
prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin
select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /
spool off
set feedback on
set embedded off
set heading on
set verify on
@tmp
-------------
That will build the generic table and package plus generate a trigger that would
look like:
SQL> @thatscript dept
create or replace trigger aud#dept
after update on dept
for each row
begin
audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME);
audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC);
end;
/
