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

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.