How to make a trigger for statement level sequence increment in Oracle?

Question

I'm trying to log a version id for each insert into the target table. Each insert may contain multiple rows. For each insert, I want to assign a unique numeric id to a specific column. What exact code may help me with that?

I've made a simple numeric sequence with INCREMENT_BY = 1. I've tried to solve my task with a single trigger which will increment my version sequence and apply it to the version column. But, according to Oracle documentation - each trigger must either operate on row level or statement level. So I came to the conclusion that I need at least 2 triggers: one to increment version sequence and another one to apply it to rows that are being inserted.

I've made multiple versions of the statement level trigger to increment my sequence, but my syntax seems to be incorrect. Here is one of the examples:

create or replace TRIGGER my_trigger
AFTER INSERT ON my_table
BEGIN
    my_sequence.NEXTVAL;
END;

This code shoots out PLS-00313: "NEXTVAL" not declared in this scope. I've also tried to use "NEXTVAL" inside "SELECT" statement, but it also was fruitless. What am I missing?

Tad Harrison · Accepted Answer · 2019-06-26 18:52:30Z

2

In order to set all records to have the exact same value from a sequence, once per INSERT, you can use a global variable in a compound trigger.

Create test table and sequence:

create table my_table(
  my_column number,
  other_column number
)
/
create sequence my_seq
/

Create the trigger:

CREATE OR REPLACE TRIGGER my_trigger
   FOR INSERT
   ON my_table
   COMPOUND TRIGGER
   g_value NUMBER;

   BEFORE STATEMENT
   IS
   BEGIN
      g_value := my_seq.NEXTVAL;
   END BEFORE STATEMENT;

   BEFORE EACH ROW
   IS
   BEGIN
      :new.my_column := g_value;
   END BEFORE EACH ROW;
END;
/

Now demonstrate

insert into my_table values(null, 10)
/
insert into my_table values(null, 20)
/
insert into my_table 
select cast(null as number) my_column, level other_column from dual connect by level < 10
/
insert into my_table values(null, 40)
/

Results:

select * from my_table
/

MY_COLUMN OTHER_COLUMN
--------- ------------
        1           10
        2           20
        3            1
        3            2
        3            3
        3            4
        3            5
        3            6
        3            7
        3            8
        3            9
        4           40

Note how the middle rows all have 3 as their MY_COLUMN value.

The BEFORE STATEMENT portion gets a single value from the sequence. The BEFORE EACH ROW part assigns that same value to every row affected by the statement.

The challenge with trying to do this with a statement-level trigger in Oracle is that you do not have access to the list of records affected, so you can't say "set the group number for records 5,6,7,8,9, and 10"

edited Jun 26, 2019 at 18:52

answered Jun 26, 2019 at 17:49

Tad Harrison

1,2937 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roman Voronov Over a year ago

Thanks, but your code increments on all rows in a single insert. I need it to increment only once per the whole insert. So each row inside a single insert must have the same sequence value.

Tad Harrison Over a year ago

@RomanVoronov, Got it. I have edited my response to address this specific need.

Roman Voronov Over a year ago

@Tad_Harrison thank you very much! Excellently elegant approach and works as intended.

Barbaros Özhan · Accepted Answer · 2019-06-26 18:01:23Z

2

You just need

:new.col1 := my_sequence.NEXTVAL;

assignment.

If you're using DB 12c, then use altering your table as

ALTER TABLE mytable MODIFY col1 INTEGER DEFAULT my_sequence.NEXTVAL; or

if you didn't add an identity column yet or need a new identity column, use

ALTER TABLE mytable ADD id INTEGER GENERATED ALWAYS AS IDENTITY;

without need of a Database Trigger.

edited Jun 26, 2019 at 18:01

answered Jun 26, 2019 at 17:39

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

4 Comments

Lukasz Szozda Over a year ago

DEFAULT as sequence is nice way :) I prefer that over triggers

Roman Voronov Over a year ago

Thanks! I've checked the version: it's 12c. "ALTER" method returns error PLS-00103: Encountered symbol "ALTER"

Barbaros Özhan Over a year ago

you're welcome @RomanVoronov. Consider Demo please.

Tad Harrison Over a year ago

@RomanVoronov, this approach will increment once per row, which you said is not what you want.

Collectives™ on Stack Overflow

How to make a trigger for statement level sequence increment in Oracle?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related