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
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;
