Oracle - Set the sequence value to max indentity value of the table within trigger
NickName:LittleBit Ask DateTime:2014-09-05T16:07:07

Oracle - Set the sequence value to max indentity value of the table within trigger

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

More about “Oracle - Set the sequence value to max indentity value of the table within trigger” related questions

Oracle - Set the sequence value to max indentity value of the table within trigger

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 ...

Show Detail

Need help converting Oracle trigger to Mariadb

I am new to mariadb trigger. I had this trigger in Oracle. Need help to convert it to mariadb: DECLARE v_newVal NUMBER(12) := 0; v_incval NUMBER(12) := 0; BEGIN IF INSERTING ...

Show Detail

How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(

Show Detail

Instead of trigger with Oracle sequence

I have some old applications (legacy applications) which uses their own MAX NUMBER table. The problem is that we cannot modify and release all the applications at the same time. And therefore, we w...

Show Detail

Oracle Create trigger statement fails with internal error code ORA-00600

Trying to execute a dumpfile created from Oracle. Everything gets created and altered fine until this block: CREATE OR REPLACE TRIGGER "LABS"."CHANNEL_CHANNEL_ID_TRG" BEFORE INSERT ON channel FOR ...

Show Detail

Oracle jdbc creating a table, sequence and trigger

I'm trying to create a table with a sequence and with a trigger for that sequence. When I do this within Oracle Express edition I have the following SQL statements. Which DO run correctly inside Or...

Show Detail

How to insert sequence value when using OracleBulkCopy in c# without before trigger in oracle

Using Oracle bulk copy in .net I am trying to bulk insert record to a table.if the table contain primary key sequence column how can I insert the value to primary key column at the time of bulk in...

Show Detail

Adding (count) sequence value to column value via trigger in Oracle SQL

I would like to write a trigger which will be able to add sequence value to column value. Example: Sequence value is 5 and column value in table is 45. Result after inserting new row should be 50.

Show Detail

PostgreSQL: Set MAX Value for Sequence to a Higher Value

Currently I am importing data from a RabbitMQ worker to a table in postgreSQL. In doing so I received this error: 4|tiq-work | error: nextval: reached maximum value of sequence "table_id_seq" (

Show Detail

Oracle update sequence value

Using Oracle 11, and using a query/queries (no stored procedure) I want to to update my sequencers value to: biggest primary key in table + 1 This is what I have but couldnt combine these two:

Show Detail