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.