Oracle Database Discussions

OracleException: ORA-04901:table ORACLE.ATTRIBUTES is mutating, trigger/function
may not see it
ORA-06512: at \"ORACLE.ATTRIBUTES_ATTRIBUTEID_TRG\",line 1-
ORA-04088: error during execution of trigger 'ORACLE.ATTRIBUTES_ATTRIBUTEID_TRG'


Hello,

I am struggling with the mutating tables error.
I cant make it an AFTER trigger since I need the right ID from the sequence BEFORE.

create or replace TRIGGER "ATTRIBUTES_ATTRIBUTEID_TRG" BEFORE INSERT OR UPDATE ON Attributes
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.AttributeID IS NULL THEN
SELECT Attributes_AttributeID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT max(AttributeID) INTO v_newVal FROM Attributes;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT Attributes_AttributeID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- save this to emulate @@identity
sqlserver_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.AttributeID := v_newVal;
END IF;
END;

Edited by: metalray on 24-Jun-2009 01:08