[askdba] Re: Doubt on after insert trigger!

  • From: Thiagoo <thiagoodba@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Tue, 6 Dec 2005 13:39:23 -0800 (PST)

Ganesh,
   
  Thanks for your mail.
   
  I'm able to commit in my trigger.
   
  When my users insert/update/delete in a master table, I need to record those 
information in another table(for ex audit table). This I've accomplished in a 
trigger, to commit inside a trigger I've used autonomous_transaction. Inside 
the trigger(as per the code attached earlier)...I'm inserting the primary key 
along with user,sysdate and the activity, so that each user's activity can be 
inserted in the table.
   
  Is there any other way..other than trigger..to do this...? Even when I think 
of other ways..its thru triggers only..using varray or plsql table.
   
  If I use trigger, using the new value am able to insert into the audit 
table..!
   
  If there's any other way ..Pls let me know!
   
  As per the current situation, it works fine with the application. When I 
check thru SQLPLUS, as I said, if I rollback the current 
one-insert/update/delete in the master table..it rollbacks and not in the audit 
table.
   
  -Thiagu
  

Ganesh Raja <ganesh.raja@xxxxxxxxx> wrote:
  Why do you want to commit in the trigger. You are saying Oxymorons here !!!

- Ganesh

On 06/12/05, Thiagoo wrote:
> Ganesh,
>
> Thanks for your reply.
>
> If I dont have that pragma, I wont be able to commit in the trigger. It will
> show the below error.
>
> ERROR at line 1:
> ORA-04092: cannot COMMIT in a trigger
> ORA-06512: at "VPRODUCT_LINE_AI_TRG", line 40
> ORA-04088: error during execution of trigger 'VPRODUCT_LINE_AI_TRG'
>
> Ganesh Raja wrote:
> Thiagoo,
>
> Why do you have that Pragma autonoums transaction.
>
> - Ganesh
>
> On 06/12/05, Thiagoo 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
> >
> >
>
>
>
>
> ==================================================
>
> Thanks & Best Regards,
>
> Thiagu
>
> ==================================================
>
> ________________________________
> Yahoo! Personals
> Single? There's someone we'd like you to meet.
> ________________________________
> Yahoo! Personals
> Single? There's someone we'd like you to meet.
> Lots of someones, actually. Try Yahoo! Personals
>
>

  



==================================================
 
Thanks & Best Regards,
 
Thiagu
 
==================================================

                
---------------------------------
 Yahoo! Personals
 Skip the bars and set-ups and start using Yahoo! Personals for free

Other related posts: