I need to get the max indentity value from the table and set the sequence to that value.
For that I'm trying to read max indentity value from the table(on which current trigger is fired) within this trigger and set the sequence to that value
But Im getting mutating error when going to read the table. Im using Oracle 11g.
So my problem is there any way to set the sequence value to max indentity value of the table within this trigger? Please advice.
Here is my trigger ;
create or replace
TRIGGER StringTextTrg BEFORE INSERT ON StringText
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.STxtID IS NULL THEN
SELECT StringText_STxtID_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 NVL(max(STxtID),0) INTO v_newVal FROM StringText;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT StringText_STxtID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- assign the value from the sequence to emulate the identity column
:new.STxtID := v_newVal;
END IF;
END;
Copyright Notice:Content Author:「LittleBit」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/25681276/oracle-set-the-sequence-value-to-max-indentity-value-of-the-table-within-trigg