[askdba] Doubt on after insert trigger!

  • From: Thiagoo <thiagoodba@xxxxxxxxx>
  • To: oracle_champions@xxxxxxxxxxxxxxx, askdba@xxxxxxxxxxxxx, dba_gurus@xxxxxxxxxxxxxxx
  • Date: Tue, 6 Dec 2005 12:27:32 -0800 (PST)

HI,
   
  I have a product_line table, if I insert/update/delete on this table, I want 
to populate the primary key of this table + user+timestamp+activity. I'm 
enforcing this with an after insert trigger with the below code.
   
  SQL Session 1:- I'm inserting a record in vproduct_line.Before commiting 
here, I can see a record in the audit table(vproduct_line_audit). If I roll 
back here(vproduct_line) it rollbacks, still the record is in the 
vproduct_line_audit. How can I do such that, only commited records should go 
the audit table. In this session autocommit is OFF. If we save thru some user 
application, this is fine, but when I try with SQ Session, I face this...How 
can I resolve this?
   
  SQL Session 2:- I'm getting a record in the vproduct_line_audit.
   
   
    create or replace trigger vproduct_line_ai_trg
  after insert or update or delete on vproduct_line 
  for each row
  declare
  pragma autonomous_transaction;
  begin
  if ( inserting ) then
  insert into 
  vproduct_line_audit(prod_cd,
  user_id,
  timestamp,
  activity)
  values(:new.prod_cd,
  user,
  sysdate,
  'INSERT');
  end if;
  if ( updating ) then
  insert into 
  vproduct_line_audit(prod_cd,
  user_id,
  timestamp,
  activity)
  values(:new.prod_cd,
  user,
  sysdate,
  'UPDATE');
  end if;
  if ( deleting ) then
  insert into 
  vproduct_line_audit(prod_cd,
  user_id,
  timestamp,
  activity)
  values(:old.prod_cd,
  user,
  sysdate,
  'DELETE');
  end if;
  commit;
  end;
  /

   
  TIA,
  Thiagu


                
---------------------------------
 Yahoo! Personals
 Single? There's someone we'd like you to meet.
 Lots of someones, actually. Yahoo! Personals

Other related posts: