2

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?

  • 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 '17 at 20:17
  • 1
    There's some information here and here for example. – mustaccio Jun 27 '17 at 20:34
  • @mustaccio And more here: stackoverflow.com/a/17211016/5576771 – Wilson Jun 27 '17 at 20:47
6

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');  <- Fill Mode
      END;
  • 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? – Wilson Jun 28 '17 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 '17 at 21:03
  • I've posed this issue as a question here: dba.stackexchange.com/questions/177546/… – Wilson Jun 28 '17 at 21:38
1

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 '17 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 '17 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 '17 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 '17 at 22:24
  • @RDFozz is right, an identity column with a virtual column will solve this issue handily. – Raj Jun 28 '17 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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