[askdba] Re: Doubt on after insert trigger!
- From: Thiagoo <thiagoodba@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Tue, 6 Dec 2005 12:56:03 -0800 (PST)
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.
Lots of someones, actually. Try Yahoo! Personals
Other related posts:
