[askdba] Re: Doubt on after insert trigger!

  • From: Nisar Tareen <ntareen@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx, oracle_champions@xxxxxxxxxxxxxxx, dba_gurus@xxxxxxxxxxxxxxx
  • Date: Tue, 6 Dec 2005 13:07:54 -0800 (PST)

Thiagoo,
   
  As you have mentioned this only happened during the SQLPLUS session, not with 
the application session. 
   
  As per my understanding  Transaction ends and commit is performed. 
   
   Transaction ends with these events. 
   
          1-     Session is closed. or disconnect from database
          2-     Manual commit or  Rollback is performed. 
   
  So the the sqlplus session end after the DDL  the session is closed without 
Commit or roolback the sql session perform a Commit as normal session end. 
   
  Intersting case, not observed commonly. 
   
  Hope my logic is correct.
   
  Nisar Tareen 
   
  

Thiagoo <thiagoodba@xxxxxxxxx> wrote:
    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 i f;
  commit;
  end;
  /

   
  TIA,
  Thiagu

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


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

Other related posts: