Trigger problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Trigger problem

  1. #1
    Join Date
    Nov 1999
    Posts
    226
    Hi

    Create or replace trigger order_hist
    before insert or update of ORPSTMP,ORUSTMP,ORSTAT on orders
    for each statement
    begin
    insert into RF_ORD_HIST
    select orrfnbr,orstat,ORUSTMP from orders;
    commit;
    end;
    /


    I am getting a table mutatiion here. Let me know what to do . Will declaring teh variables help me here

    Let me know

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What are you trying to do?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    is there any trigger on RF_ORD_HIST ?

    - Rajeev

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by puneet [/i]
    [B]Hi

    Create or replace trigger order_hist
    before insert or update of ORPSTMP,ORUSTMP,ORSTAT on orders
    for each statement
    begin
    insert into RF_ORD_HIST
    select orrfnbr,orstat,ORUSTMP from orders;
    commit;
    end;
    /


    I am getting a table mutatiion here. Let me know what to do . Will declaring teh variables help me here

    Let me know [/B][/QUOTE]

    1. If you wouldn't get table-mutating error, you would get some other ORA message telling you that you can't use COMMIT or ROLLBACK insde a trigger (unles you would implement it as autonomus transaction).

    2. There is something wrong with the logic of your trigger. Before any insert statement you are trying to save some column values from *all* of the rows of your triggering table (you have no WHERE clause!) - is this realy what you want to do?

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 1999
    Posts
    226
    Hi Back

    Nope I just want the following values in ord_hist table whenever the orstat in orders table is updated or a new row is inserted in it .

    Create or replace trigger order_hist
    before insert or update of ORSTAT on orders
    for each row
    begin
    insert into RF_ORD_HIST
    select orrfnbr,orstat,sysdate from orders;
    end;

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    If you want to insert sysdate and new values of orrfnbr and orstat, in ord_hist table on every update of orstat and insert on orders, use :new qualifier for new values.

    CREATE OR REPLACE TRIGGER order_hist
    BEFORE INSERT OR UPDATE OF orstat ON orders
    FOR EACH ROW
    BEGIN
    INSERT INTO rf_ord_hist
    VALUES(:new.orrfnbr,:new.orstat,sysdate);
    END;
    /

Posting Permissions

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



Click Here to Expand Forum to Full Width