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

        SELECT 'LC' || LPAD(TO_CHAR(CAST(SUBSTR(MAX(PROJECT_ID),-6) AS NUMBER(15,0)) + 1),6, '0')

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?

  • 4
    Never use things like select max(id) + 1 to generate sequential values -- you will save yourself and others from incalculable pain and suffering.
    – mustaccio
    Jun 27, 2017 at 20:17
  • 1
    There's some information here and here for example.
    – mustaccio
    Jun 27, 2017 at 20:34
  • @mustaccio And more here: stackoverflow.com/a/17211016/5576771
    – User1974
    Jun 27, 2017 at 20:47

2 Answers 2


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

    -- Fire trigger only if statement doesn't set project_id     
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');  <- Fill Mode
  • 1
    What happens if a user manually overrides the ID that is generated by the sequence/trigger? Let's say, to a higher number than has yet been used. When the sequence/trigger get's to that higher number, and attempts to use it, will it throw an error?
    – User1974
    Jun 28, 2017 at 18:58
  • @Wilson: the error will be thrown if there is a unique constraint (or unique index) on project_id field.
    – a1ex07
    Jun 28, 2017 at 21:03
  • I've posed this issue as a question here: dba.stackexchange.com/questions/177546/…
    – User1974
    Jun 28, 2017 at 21:38

Note that a table can have more than one unique column.

I'd include an autoincrement column in the table, and then use that value to build the business ID ('LC' + RIGHT(CAST(1000000 + myNumericID as varchar(10)),6)). I'd prefer to use the autoincrement column as the table's primary key, and make the business ID a computed column (or whatever it's called in Oracle). If you have to have it as a non-virtual column, and the value of an Oracle IDENTITY column happens to be available when a trigger executes, you could build it in the trigger; otherwise, you'd need to use a sequence instead of an IDENTITY columns (see a1ex07's answer). (I don't have access to an Oracle 12c instance, so I can't be sure that the value is available when the trigger executes)

Note that, with your trigger, it might be possible for two people to save at the same time, see the same max value for the key, and generate the same key for their rows. If that is the primary key (or a unique column), one row wouldn't be saved; if not, you'd have two rows with the same value, which is of course Bad.

  • "but you could probably build the value in an AFTER trigger" - you cannot modify the row inside AFTER trigger.
    – a1ex07
    Jun 27, 2017 at 19:54
  • Updated to remove note on AFTER triggers, and to note uncertainty as to IDENTITY value's available in a trigger. Pointer to @a1ex07's answer, if you can't use the IDENTITY value in the trigger.
    – RDFozz
    Jun 27, 2017 at 20:35
  • @RDFozz .. autoincrement columns are only available with Oracle 12.1 and beyond. We don't know what version the OP is working with. I think your overall approach is overly complex, given the OP's situation, but your comments about taking "max" values in a multi-user environment are spot on.
    – EdStevens
    Jun 27, 2017 at 22:12
  • OP is tagged oracle-12c - of course, it could be v 12.0.x. If it was tagged as 11 or earlier, I would have gone for a sequence solution too, since that was how you faked autoincrement columns at that point.
    – RDFozz
    Jun 27, 2017 at 22:24
  • @RDFozz is right, an identity column with a virtual column will solve this issue handily.
    – Raj
    Jun 28, 2017 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.