Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Mutating Table/Trigger help

    Hello, while doing some work on a project, i came across a problem that set me down.

    Let me get the information flowing.

    The tables involved:

    /* ------------------------------------------------------------------- */
    /* Add table "TREATMENT" */
    /* ------------------------------------------------------------------- */

    create table TREATMENT
    (
    TRE_NO VARCHAR2(4) NOT NULL,
    TRE_TYPE VARCHAR2(20),
    TRE_DESC VARCHAR2(60),
    TRE_RATE NUMBER(5,2)
    );
    alter table Treatment add constraint PK_Treatment primary key (TRE_NO);

    /* ------------------------------------------------------------------- */
    /* Add table "T_SESSION" */
    /* ------------------------------------------------------------------- */

    create table T_SESSION
    (
    T_SESNO VARCHAR2(5) NOT NULL,
    STF_NO VARCHAR2(4),
    TRE_NO VARCHAR2(4),
    BKN_NO VARCHAR2(4),
    TS_TOTAL NUMBER(7,2)
    );
    alter table T_Session add constraint PK_Session primary key (T_SESNO);
    alter table T_Session add constraint FK_Session_Staff foreign key(STF_NO) references Staff(STF_NO);
    alter table T_SESSION add constraint FK_SESSION_BOOKING foreign key(BKN_NO) references BOOKING(BKN_NO);

    So, what i wanted to do was to use a function, procedure, cursor and a trigger to call up a TRE_RATE from the TREATMENT table every time a record with TRE_NO is created in the T_SESSION table. Unfortunately, with my limited knowledge, i was not able to make the series of codes functional.

    Here is the code that i came up with:

    CREATE OR REPLACE FUNCTION T_TOTAL (
    i_TRE_NO T_SESSION.TRE_NO%TYPE)

    RETURN NUMBER
    IS

    v_T_TOTAL T_SESSION.TS_TOTAL%TYPE;


    BEGIN

    SELECT TRE_RATE
    INTO v_T_TOTAL
    FROM TREATMENT
    WHERE TRE_NO = i_TRE_NO;

    IF v_T_TOTAL IS NULL THEN
    v_T_TOTAL := 0;

    END IF;



    RETURN v_T_TOTAL;

    END;
    /


    CREATE OR REPLACE PROCEDURE TS_TOTAL
    AS
    CURSOR c_T_SESSION
    IS
    SELECT TRE_NO
    FROM T_SESSION;

    v_TRE_NO T_SESSION.TRE_NO%TYPE;
    v_TS_TOTAL T_SESSION.TS_TOTAL%TYPE;

    BEGIN
    OPEN c_T_SESSION;

    LOOP
    FETCH c_T_SESSION INTO v_TRE_NO;

    EXIT WHEN c_T_SESSION%NOTFOUND;

    v_TS_TOTAL := T_TOTAL(v_TRE_NO);

    UPDATE T_SESSION
    SET TS_TOTAL = v_TS_TOTAL
    WHERE TRE_NO = v_TRE_NO;

    END LOOP;

    CLOSE c_T_SESSION;
    END;
    /

    CREATE OR REPLACE TRIGGER RECALC_TS_TOTAL
    AFTER INSERT OR DELETE OR
    UPDATE OF TRE_NO ON T_SESSION
    FOR EACH ROW

    DECLARE
    v_TRE_NO T_SESSION.TRE_NO%TYPE;
    v_TS_TOTAL T_SESSION.TS_TOTAL%TYPE;

    BEGIN
    IF DELETING OR
    UPDATING THEN
    SELECT TS_TOTAL
    INTO v_TS_TOTAL
    FROM T_SESSION
    WHERE TRE_NO = ld.TRE_NO;
    UPDATE T_SESSION
    SET TS_TOTAL = v_TS_TOTAL - (ld.TS_TOTAL)
    WHERE TRE_NO = ld.TRE_NO;
    END IF;

    IF INSERTING OR
    UPDATING THEN
    SELECT TS_TOTAL
    INTO v_TS_TOTAL
    FROM T_SESSION
    WHERE TRE_NO = :new.TRE_NO;

    UPDATE T_SESSION
    SET TS_TOTAL = v_TS_TOTAL + (:new.TS_TOTAL)
    WHERE TRE_NO = :new.TRE_NO;
    END IF;
    END;
    /

    As you can see i am trying to modify a value in the table the trigger is being triggered.

    I also know that if i change the code to a statement level trigger it could get rid of the mutating trigger error but i do not know how to port this functionality.

    Please help me out~

    Thank you in advance.

    dklhs90

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    https://forums.oracle.com/forums/thr...992311#9992311

    >i do not know how to port this functionality.
    What does this mean?
    Port what functionality to where exactly?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    Quote Originally Posted by anacedent View Post
    https://forums.oracle.com/forums/thr...992311#9992311

    >i do not know how to port this functionality.
    What does this mean?
    Port what functionality to where exactly?
    By port, i was trying to say that i do not have enough knowledge to take the row level trigger and turn it into a statement level trigger.

Tags for this Thread

Posting Permissions

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