Trigger to get next prefixed ID: Is there a better way? I have a projects table:

+------------+-----+
| 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 (LC). 

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.

I've written a trigger that automatically inserts the next PROJECT_ID when a new row is created in the application's attribute editing environment (more here). 

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?
I expect your trigger to throw runtime "Mutating table" error . Unless autonomous transaction is used because you cannot query the table that fires the row-level trigger inside its body.  

I'd rather keep it simple - create a sequence, for instance ,  CREATE SEQUENCE SEQ_PROJECT START WITH 6 INCREMENT BY 1. 
Also, I'd fire trigger only if project_id is not set (WHEN clause) . 

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
    -- Fire trigger only if statement doesn't set project_id     
    WHEN (NEW.PROJECT_ID IS NULL) 
      BEGIN
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');  &lt;- Fill Mode
      END;