I want to create a table, with composite primary key and autoincrement, in Oracle 19c, like this:
pk01 pk02 column1
==== ==== =======
1 1 abc
1 2 def
1 3 ghi
2 1 jkl
2 2 mno
3 1 pqr
1 4 stu
How I do it?
Create trigger on this table to achieve this result. Assume table name is test123
create or replace trigger trg_test123 before insert on test123
for each row
declare
v_id1 number;
v_max_id2 number;
begin
v_id1 := :new.id1;
select nvl(max(id2), 0) into v_max_id2 from test123 where id1 = v_id1;
v_max_id2 := v_max_id2 +1;
:new.id2 := v_max_id2;
end;