I have a
+------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | | | LC000003 | | | LC000004 | | | LC000005 | | +------------+-----+
As you can see, the
PROJECT_ID column has a text datatype (
NVARCHAR2) and it has a prefix (
I'm guessing that creating an ID with a prefix might have been a been a poor design choice. But, right or wrong, this is how my organization has set up it's business.
CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS FOR EACH ROW BEGIN SELECT 'LC' || LPAD(TO_CHAR(CAST(SUBSTR(MAX(PROJECT_ID),-6) AS NUMBER(15,0)) + 1),6, '0') INTO :NEW.PROJECT_ID FROM PROJECTS; END;
However, this is literally the very first trigger I have ever written. The fact that it deconstructs the textual ID, get's the next number, and then reconstructs the textual ID, seems overly complicated to me. I can't help but think Oracle might have a more sophisticated way of handling this.
Is there a better way?
I have a subsequent, related question here: Can an Oracle IDENTITY column be nullable?