[askdba] Re: Doubt on after insert trigger!
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Tue, 6 Dec 2005 20:35:05 +0000
Thiagoo,
Why do you have that Pragma autonoums transaction.
- Ganesh
On 06/12/05, 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
>
>
Other related posts:
