Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    61

    Unanswered: trigger update another column in the same table

    Hi all,

    I have a serious problem:

    1/ create table mm (a int, b int, c int);

    2/ create or replace trigger t_mm after update of b on mm
    referencing old as old new as new
    for each row
    declare
    i int;
    j int;
    pragma autonomous_transaction;

    begin
    i := ld.b;
    j := :new.b;
    if i<> j then
    update mm
    set c = 2
    where a = :new.a ;
    end if;

    end;

    3/ insert into mm (a, b) values (1, 1);

    4/ update mm set b= 2 where a =1

    when I run step 4, it raise an error :

    Ora-00060: deadlock detected while waiting for resouce
    Ora-06512: at "t_mm" line 10
    Ora-04088:error during execution of trigger "t_mm"

    what is the problem? how do I solve it?

    thanks in advanced
    Thi Nguyen

  2. #2
    Join Date
    Sep 2004
    Posts
    17
    Hi,
    instead of writing an after update trigger use a before update trigger and instead of updating the table just use this stmt to change the value of c:
    :new.c := 2;

    Alex

  3. #3
    Join Date
    Sep 2004
    Posts
    17
    Oh, now I see the real problem, it's your autonomous transaction. The main transactio locks the row and the autonomous transaction tries to lock it too. I think, you don't need the autonomous transaction ...

    Alex

  4. #4
    Join Date
    Jul 2004
    Posts
    61
    Hi Alex

    if I don't use "pragma autonomous_transaction;"
    It will raise other error:
    Ora-04091:table mm is mutating, trigger/function may not see it.

    how do I update another column in the same table in a trigger?

    Thanks
    thi Nguyen

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by thinguyen
    Hi Alex

    if I don't use "pragma autonomous_transaction;"
    It will raise other error:
    Ora-04091:table mm is mutating, trigger/function may not see it.

    how do I update another column in the same table in a trigger?

    Thanks
    thi Nguyen
    YOu are missing the entire point. If you want to modify the value use the following trigger

    create or replace trigger t_mm before update of b on mm
    for each row
    begin
    if ld.b <> :new.b then
    :new.c := 2;
    end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2004
    Posts
    61
    Hi beilstwh

    That is excellent help for me.

    Thanks a lot,

    Thi Nguyen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •