On Thursday, April 9, 2015 at 8:00:39 AM UTC-6, sundar wrote:
>
> Hi,
>
> I need to create audit history table on a table with lots of
> columns(200). I am creating trigger like below.
>
> CREATE OR REPLACE TRIGGER ...
> ... UPDATE OF Sal, Comm ON Emp_tab ...
> BEGIN
>
> ... IF UPDATING ('SAL') THEN
> insert into audit_table...
> END IF;
> END;
>
> Is there any better way instead of checking each column whether it is
> updated or not.
>
> Regards,
> Sundar
>
Here's an example of how I would do it:
set echo on
create table part (
part_ser_no number not null,
part_no number not null,
part_desc varchar2(40) not null,
part_cond varchar2(10) not null,
part_hours number not null,
part_cycles number not null,
constraint part_pk_ser primary key(part_ser_no, part_no));
create unique index part_ser_uniq
on part(part_ser_no);
create table engine(
eng_ser_no number not null,
eng_hours number not null,
eng_cycles number not null,
eng_part_no number not null,
eng_part_ser number not null,
eng_last_maint_dt date not null,
eng_last_mechanic number not null,
constraint eng_pk_ser primary key(eng_ser_no),
constraint eng_part_fk foreign key(eng_part_ser, eng_part_no) references
part);
create table part_hist (
part_ser_no number not null,
part_no number not null,
part_desc varchar2(40) not null,
part_cond varchar2(10) not null,
part_hours number not null,
part_cycles number not null,
username varchar2(30),
mod_dt date,
action varchar2(6));
create table engine_hist (
eng_ser_no number not null,
eng_hours number not null,
eng_cycles number not null,
eng_part_no number not null,
eng_part_ser number not null,
eng_last_maint_dt date not null,
eng_last_mechanic number not null,
username varchar2(30),
mod_dt date,
action varchar2(6));
create or replace trigger part_hist_trg
after insert or update or delete
on part
for each row
begin
if inserting then
insert into part_hist
(
part_ser_no ,
part_no ,
part_desc ,
part_cond ,
part_hours ,
part_cycles ,
username ,
mod_dt ,
action
)
values
(
:new.part_ser_no ,
:new.part_no ,
:new.part_desc ,
:new.part_cond ,
:new.part_hours ,
:new.part_cycles ,
user ,
sysdate ,
'INSERT'
);
elsif updating then
insert into part_hist
(
part_ser_no ,
part_no ,
part_desc ,
part_cond ,
part_hours ,
part_cycles ,
username ,
mod_dt ,
action
)
values
(
:new.part_ser_no ,
:new.part_no ,
:new.part_desc ,
:new.part_cond ,
:new.part_hours ,
:new.part_cycles ,
user ,
sysdate ,
'UPDATE'
);
elsif deleting then
insert into part_hist
(
part_ser_no ,
part_no ,
part_desc ,
part_cond ,
part_hours ,
part_cycles ,
username ,
mod_dt ,
action
)
values
(
:old.part_ser_no ,
:old.part_no ,
:old.part_desc ,
:old.part_cond ,
:old.part_hours ,
:old.part_cycles ,
user ,
sysdate ,
'DELETE'
);
end if;
end;
/
show errors
create or replace trigger engine_hist_trg
after insert or update or delete
on engine
for each row
begin
if inserting then
insert into engine_hist
(
eng_ser_no ,
eng_hours ,
eng_cycles ,
eng_part_no ,
eng_part_ser ,
eng_last_maint_dt ,
eng_last_mechanic ,
username ,
mod_dt ,
action
)
values
(
:new.eng_ser_no ,
:new.eng_hours ,
:new.eng_cycles ,
:new.eng_part_no ,
:new.eng_part_ser ,
:new.eng_last_maint_dt ,
:new.eng_last_mechanic ,
user ,
sysdate ,
'INSERT'
);
elsif updating then
insert into engine_hist
(
eng_ser_no ,
eng_hours ,
eng_cycles ,
eng_part_no ,
eng_part_ser ,
eng_last_maint_dt ,
eng_last_mechanic ,
username ,
mod_dt ,
action
)
values
(
:new.eng_ser_no ,
:new.eng_hours ,
:new.eng_cycles ,
:new.eng_part_no ,
:new.eng_part_ser ,
:new.eng_last_maint_dt ,
:new.eng_last_mechanic ,
user ,
sysdate ,
'UPDATE'
);
elsif deleting then
insert into engine_hist
(
eng_ser_no ,
eng_hours ,
eng_cycles ,
eng_part_no ,
eng_part_ser ,
eng_last_maint_dt ,
eng_last_mechanic ,
username ,
mod_dt ,
action
)
values
(
:old.eng_ser_no ,
:old.eng_hours ,
:old.eng_cycles ,
:old.eng_part_no ,
:old.eng_part_ser ,
:old.eng_last_maint_dt ,
:old.eng_last_mechanic ,
user ,
sysdate ,
'DELETE'
);
end if;
end;
/
set echo off
show errors
set timing on
insert all
into part
(part_ser_no,
part_no,
part_desc,
part_cond,
part_hours,
part_cycles)
values
(1,
432,
'Flarp flap',
'Decent',
12,
8)
into part
(part_ser_no,
part_no,
part_desc,
part_cond,
part_hours,
part_cycles)
values
(2,
433,
'Flarp flap flipper',
'Decent',
12,
8)
select * from dual;
insert into engine
(eng_ser_no,
eng_hours,
eng_cycles,
eng_part_no,
eng_part_ser,
eng_last_maint_dt,
eng_last_mechanic)
values
(9999999,
44,
32,
432,
1,
sysdate,
56);
delete from part
where part_no = 433;
commit;
set linesize 132 timing off
select * from part;
select * from engine;
select * From part_hist;
select * from engine_hist;
drop table part purge;
drop table engine purge;
drop table part_hist purge;
drop table engine_hist purge;
drop trigger part_hist_trg;
drop trigger engine_hist_trg;
set linesize 80
David Fitzjarrell
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
