Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Unhappy Unanswered: Trigger on update

    I`ve got tble TblFlight with column Available_Seats. I`ve got table ordered_ticket. For each ordered ticket the Value in Available_Seats will decrease by 1. I wish to have at trigger that every time Available_Seats are updated will this message come up. "'There are'" [number of seats available] ' available)


    WHAT CAN I DO to solve this problem?

    this doesn`t whork: I receive the following message:

    Tbl Flight is mutating, the trigger/function may not see it.

    CREATE OR REPLACE TRIGGER IN601G79.VIS_LEDIGE
    BEFORE UPDATE OF Available_SEats ON IN601G79.tblflight
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    DECLARE
    RCOUNT NUMBER;
    BEGIN
    SELECT COUNT(LEDIGSETER) INTO RCOUNT FROM TBLFLYVNING;
    IF RCOUNT < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, There are [number of seats] available');
    END IF;
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger on update

    You could solve the mutating problem by removing the FOR EACH ROW clause - in fact, if the user updates 7 rows at once, presumably you would only want to display a message once anyway, rather than:
    There are 42 seats available
    There are 41 seats available
    There are 40 seats available
    There are 39 seats available
    There are 38 seats available
    There are 37 seats available
    There are 36 seats available

    However, using RAISE_APPLICATION_ERROR is not appropriate, as this will ROLLBACK the update!

    There is no way to directly display a message to the user from a trigger. If you just want to inform the user who made the update, then move this logic to the client (application). Alternatively you could look into using the DBMS_ALERT package to notify users of updates; this will only send an alert on COMMIT, which makes sense when you think about it.

  3. #3
    Join Date
    Nov 2002
    Posts
    5
    Someone told me: You don`t need select or rcount. test directly on
    :NEW.Available seats instead . the trigger has access to
    (:OLD) and the new(:NEW) when it firees AFTER UPDATE on TBLFLIGHT. Ergo you`ve already got the value that you need.

    TBLFlight(*Flight_ID, Distance, Date, Available_Seats)

    I must admit that I`m not sure what this means. If it`s as I understand:
    (there are something I`m missing. please enlighten me.) what am I Doing wrong.

    CREATE OR REPLACE TRIGGER IN601G79.VIS_LEDIGE
    AFTER update ON IN601G79.TBLFLIGHT
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    BEGIN
    IF AVAILABLE_SEATS > 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'THERE ARE || :new.LEDIGSETER|| LEFT);
    END IF;
    --show_errors trigger"VIS_LEDIGE";
    END;
    /
    RUN;
    SHOW ERRORS TRIGGER "IN601G79.VIS_LEDIGE";

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, virtually ALL of my comments from my previous answer still apply. This is still NOT GOING TO WORK!!!

    RAISE_APPLICATION_ERROR raises (as it name suggests) an ERROR, i.e.:

    ORA-20001: THERE ARE xxx LEFT

    Because it is an ERROR, the statement that caused the error (i.e. the UPDATE that fired the trigger) will be rolled back - it will not happen!

    So by trying to issue a message, you have aborted the update. That's why I said you should either do this message in the client application, or look at using DBMS_ALERT instead. Re-read my previous answer for more details.

  5. #5
    Join Date
    Nov 2002
    Posts
    5
    Ok, thank you. This helped.

Posting Permissions

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