Need assistance in trigger creation

[A Sundar]

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

[ddf]

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

[Michael Moore]

The only thing I would do differently is to use TIMESTAMP(6) instead
of SYSDATE because sometimes SYSDATE is not granular enough to
maintain the proper order.

> --
> --
> 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...@googlegroups.com
> To unsubscribe from this group, send email to
> Oracle-PLSQL...@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...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>