[askdba] Re: Doubt on after insert trigger!
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Tue, 6 Dec 2005 21:05:23 +0000
Why do you want to commit in the trigger. You are saying Oxymorons here !!!
- Ganesh
On 06/12/05, Thiagoo <thiagoodba@xxxxxxxxx> 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 <ganesh.raja@xxxxxxxxx> 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
>
>
Other related posts:
