Hi Wonderful Team,

I have an oracle sequence say s.

I have table t ( id number , n number );

I need to insert the Id column with the sequence s such that the value is the same for the entire transaction thru a "Trigger"

So this Id changes only for new batch inserts.

If I use s.nextval it will insert a new value for each row. I want the same value for that insert.

I cant use s.currval as value is not initialized.

I dont want pl/sql solution( declare v number := s.nextval ; begin insert into t(id, n) select v,object_id from user_objects ;commit;)

This has to happen in trigger

How to do it ?

create sequence s ;
create table t( id number , n number ) ;

Thanks

create sequence s ;
create table t( id number not null, n number ) ;

create or replace trigger trg 
for insert on t compound trigger

  t_id t.id%type := s.nextval;
  
  before each row is 
  begin
    :new.id := t_id;
  end before each row;
end;
/
insert into t(n) select object_id from user_objects ;

select * from t
where  rownum < 6;

ID  N        
1   131,679  
1   97,493   
1   131,678  
1   131,680  
1   131,681