Oracle Database - Sequence

> Database > Oracle Database

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

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

4 - Bad Practice

4.1 - 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.