Hai Friends,
Any one help me out?...

I have a written a trigger for a table.. I got an error at the time of testing that trigger...

The error gives as

ORA-04091: table REF_COST_CENTRE is mutating, trigger/function may not see it
ORA-06512: at T_DU_REF_COST_CENTRE", line 41
ORA-04088: error during execution of trigger T_DU_REF_COST_CENTRE'

I know that mutating means... we are updating a row, which is selected by the trigger...

How to overcome this problem..


My trigger is

CREATE OR REPLACE TRIGGER T_DU_REF_COST_CENTRE
BEFORE DELETE OR UPDATE ON REF_COST_CENTRE FOR EACH ROW
DECLARE
lv_numrows INTEGER;
lv_numrowsmst INTEGER;
BEGIN
IF DELETING THEN
SELECT COUNT(*) INTO lv_numrows
FROM M_BP_COST_CENTRE
WHERE IE_COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;

IF lv_numrows > 0 THEN
raise_application_error(-20002,
'CANNOT DELETE FROM REF_COST_CENTRE TABLE
BECAUSE VALUE IS EXISTING IN
M_BP_COST_CENTRE TABLE');
END IF;
END IF;

IF UPDATING THEN
IF :NEW.COST_CENTRE_CODE
<> :OLD.COST_CENTRE_CODE THEN
SELECT COUNT(*) INTO lv_numrows
FROM M_BP_COST_CENTRE
WHERE IE_COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;

IF lv_numrows > 0 THEN
SELECT COUNT(*) INTO lv_numrowsmst
FROM REF_COST_CENTRE
WHERE COST_CENTRE_CODE
= :OLD.COST_CENTRE_CODE
AND COUNTRYCODE = :OLD.COUNTRYCODE;

IF lv_numrowsmst = 1 THEN
raise_application_error(-20002,
'CANNOT UPDATE REF_COST_CENTRE TABLE
BECAUSE VALUE IS EXISTING IN
M_BP_COST_CENTRE TABLE');
END IF;
END IF;
END IF;



Thanx..

Palani Kumar. R