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?
Advertisement
Answer
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;