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.
On 4/9/15, ddf <orat...@msn.com> wrote:
>
>
> 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.
>
--
--
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.
