Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Unanswered: Student Question

    Hi, I have a very easy student question. (Hope its easy!)

    I am trying to do the following...
    Before you update the PART table each row in that table with a warehouse equal to one set the price of that part to $100

    CREATE OR REPLACE TRIGGER TRG_PART_UPDATE
    BEFORE UPDATE ON PART
    BEGIN
    UPDATE PART
    SET PRICE =100
    WHERE WAREHOUSE=1;
    END;

    But if I make any sort of update such as.

    UPDATE PART
    SET PART_NUM ='AT99'
    WHERE PART_NUM ='AT91';

    I get the following error...

    ORA-06512: at "SYSTEM.TRG_PART_UPDATE", line 2
    ORA-04088: error during execution of trigger 'SYSTEM.TRG_PART_UPDATE'
    ORA-06512: at "SYSTEM.TRG_PART_UPDATE", line 2
    ORA-04088: error during execution of trigger 'SYSTEM.TRG_PART_UPDATE'
    ORA-06512: at "SYSTEM.TRG_PART_UPDATE", line 2
    ORA-04088: error during execution of trigger 'SYSTEM.TRG_PART_UPDATE'
    ORA-06512: at "SYSTEM.TRG_PART_UPDATE", line 2
    ORA-04088: error during execution of trigger 'SYSTEM.TRG_PART_UPDATE'
    ORA-06512: at "SYSTEM.TRG_PART_UPDATE", li1. UPDATE PART
    2. SET PART_NUM ='AT99'
    3. WHERE PART_NUM ='AT91';

    Is this because I am creating a recursive statement and it continually updates itself? I looked up the error message via google but it provides no help.

    Thanks for any help!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thats not how you would write a trigger. Do it as follows

    Code:
    CREATE OR REPLACE TRIGGER TRG_PART_UPDATE
    BEFORE UPDATE ON PART
    FOR EACH ROW
    BEGIN
    if :new.warehouse = 1 then
      :new.price := 100;
    end if;
    END;
    You may not perform an update on the same table that the trigger is written for.
    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
  •