Oracle: Trigger to insert values into another Table with one additional auto increment primary key column

Question

I have one Oracle table say

Table1
(
roll_number,
myTimestamp
)

I have another Oracle table

Table2
(
roll_number,
myTimestamp,
recordid --> this is supposed to be an auto increment primary key
)

What I want is a trigger which inserts the record inserted into Table1 into Table2 as well but with the additional primary key integer column recordid being incremented and inserted automatically. I am able to insert records without Table2 having this additional primary key column constraint using a trigger but when I try this additional column its giving issues.

I have tried creating an auto increment sequence (say MySeq) and tried to insert to create a trigger

create trigger MyTrigger
after insert on Table1
for each row
begin
  insert into Table2 values ( :new.roll_number,:new.myTimestamp,select MySeq.nextval into :new.recordid  from dual );
end;

but no luck. Thanks in advance

Answer 1

There's nothing wrong with it - at least, it works properly on my 11gXE (which database version do you use?):

SQL> create table table1 (roll_number number, mytimestamp date);

Table created.

SQL> create sequence myseq;

Sequence created.

SQL> create table table2 (roll_number number, mytimestamp date, recordid number);

Table created.

SQL> create or replace trigger mytrigger
  2    after insert on table1
  3    for each row
  4  begin
  5    insert into table2 values
  6      (:new.roll_number, :new.mytimestamp, myseq.nextval);
  7  end;
  8  /

Trigger created.

SQL> insert into table1 values (1, sysdate);

1 row created.

SQL> select * From table2;

ROLL_NUMBER MYTIMESTAMP           RECORDID
----------- ------------------- ----------
          1 12.03.2021 07:14:42          1

SQL>

---

Alternatively, instead of VALUES use SELECT:

SQL> create or replace trigger mytrigger
  2    after insert on table1
  3    for each row
  4  begin
  5    insert into table2
  6      select :new.roll_number, :new.mytimestamp, myseq.nextval
  7      from dual;
  8  end;
  9  /

Trigger created.

SQL> insert into table1 values (2, sysdate);

1 row created.

SQL> select * From table2;

ROLL_NUMBER MYTIMESTAMP           RECORDID
----------- ------------------- ----------
          1 12.03.2021 07:14:42          1
          2 12.03.2021 07:16:00          2

SQL>

Answer 2

Please try this:

create or replace trigger MyTrigger after insert on Table1 for each row

DECLARE v_recordid int;

BEGIN select MySeq.nextval into v_recordid from dual;

insert into Table2 (roll_number,myTimestamp,recordid) values ( :new.roll_number,:new.myTimestamp,v_recordid ); end;