0

I do have 3 tables. I want to generate ID for each below table and that should be unique across each table. Table1 ID - primary ID 1 -> 2 -> 3 Table2 ID - primary ID 4 -> 5 Table3 ID - primary ID 6 -> 7 -> 8 Whenever a new entry is made to the above tables it should generate unique values across the tables For next time when I want to insert 2 records to table 1 it should be Table1 ID - primary ID 9 -> 10.

Do we can create a trigger to accomplish this in Oracle

1 Answers1

0

Use a trigger on each table and create a Sequence

CREATE SEQUENCE seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

Then on the trigger on insert do something like:

   select seq.nextval 
     into :new.id
     from dual;

This was asked before. Look here

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT 
  ON qname
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  WHEN (new.qname_id is null)
DECLARE
  v_id qname.qname_id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT qname_id_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.qname_id := v_id;
END my_trigger;
Community
  • 1
  • 1
vercelli
  • 4,717
  • 2
  • 13
  • 15
  • Can you help me to create the trigger for the tables ? and how this sequence is linked to the trigger – user2677675 Jul 28 '16 at 17:04
  • @user2677675 I posted a sample I extracted from SO. – vercelli Jul 28 '16 at 17:07
  • In real scenario I do have more than 20+ table.hence I need to create 20+ triggers. Is there any way that I can create a single trigger upon 20+ table to generate the seq no – user2677675 Jul 29 '16 at 13:07
  • @user2677675 No, you will have to create 20+ triggers. one on each table. They are almost the same, you can do that dynamically. – vercelli Jul 29 '16 at 13:26