Oracle Database - Sequence

About

A highly scalable sequence because it's:

  • sorted.
  • non-blocking ID generator.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
CREATE SEQUENCE MY_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

Primary Key Trigger

Primary Key Trigger

create or replace TRIGGER "TRIGGER_NAME" BEFORE INSERT ON "TABLE_NAME" FOR EACH ROW
BEGIN 
    IF :new.PK_ID IS NULL THEN
		select SEQUENCE_NAME.nextval into :new.PK_ID from dual;
	END;
END;

Bad Practice

With a max

INSERT INTO gfx_suggestion (suggestion, suggestion_id, timestamp, 
suggestion_type_fl,
name, email, business_unit_key) select 'test suggestion. RJ 04/19/01', 
max(suggestion_id)+1, sysdate, 'T', 'Ron Jennings' , '[email protected]', '5' from 
gf_suggestion

When two people insert at about the same time with a max function, they both get the SAME suggestion_id.


Powered by ComboStrap