Menu de navegação

[airoospParticipante]

Boa tarde,

Pesquisando sobre trigger de log encontrei no site do Tom Kyte (http://asktom.oracle.com/pls/apex/f?p=1 … 9412348055) o exemplo abaixo:

sql*plus script like:

——————————————————————–
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 || ‘, 😮 ld.’ ||
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, 😮 ld.DEPTNO);
audit_pkg.check_val( ‘dept’, ‘DNAME’, :new.DNAME, 😮 ld.DNAME);
audit_pkg.check_val( ‘dept’, ‘LOC’, :new.LOC, 😮 ld.LOC);
end;

O problema é que não estou conseguindo criar o arquivo TMP.SQL corretamente.

Alguém já viu este exemplo?

Obrigado.

Airton

[Autor]

Posts