Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Smile Unanswered: Update Statement inside a Trigger

    Hi

    I want to use an update statement on the same table, inside a trigger
    I am getting error like this.

    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource
    ORA-06512: at "PWRADMIN.MY_TRIGGER", line 21
    ORA-04088: error during execution of trigger 'PWRADMIN.MY_TRIGGER'


    I have put a proper condition to avoid trigger getting fired. at the begining

    I am putting my code here

    can anybody please see any apparent problem with the concept.



    CREATE OR REPLACE TRIGGER MY_TRIGGER
    after update on MY_TABLE

    for each row
    when (new.pnlid not like old.pnlid)

    declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    id varchar2(21);

    cursor idcursor is
    select id from j48pltequip where pnlid = :new.id ;

    begin
    open idcursor;
    loop
    fetch idcursor into id;
    exit when idcursor%notfound;

    update MY_TABLE -- Trigger is being called again here, this is a
    problem.
    set
    bldg = 'BSA'
    where id = id;

    end loop;
    close idcursor;
    end;




    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps, if the whole trigger code looked like
    Code:
    begin
      :new.bldg := 'BSA';
    end;
    things would have been improved.

    By the way, what's the purpose of the pragma you used?

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Hi LittleFoot,

    what you suggested is correct;

    i have used pragma to avoid mutating error,

    but still i have a problem, the data updation is happening for :new.id only
    but i want the data updation should happen for the current id from the cursor

    how to put condition for "where id = current id from curosr"


    please help me out


    cursor idcursor is
    select id from j48pltequip where pnlid = :new.id ;

    begin

    open idcursor;

    loop

    fetch idcursor into id;
    exit when idcursor%notfound;

    -- update J48PLTEQUIP -- Trigger is being called again here, this is a problem.
    -- set

    :new.bldg := 'BBB'; -- this updation is happening for :new.id, but i wnat it for id
    -- assigning :new.id := id is creating problem

    -- where id = id; -- how to get this condition???

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A row level trigger opperates on the current record only. the new statement

    :new.bld := 'BSA';

    will set the new record being put in ONLY.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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