I have a sequence and trigger that provide auto-incrementing IDs to a
PROJECTS +------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | | | LC000003 | | | LC000004 | | | LC000005 | | +------------+-----+ CREATE SEQUENCE SEQ_PROJECT START WITH 1 INCREMENT BY 1 CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS FOR EACH ROW WHEN (NEW.PROJECT_ID IS NULL) BEGIN :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000'); END;
The sequence and trigger work just fine under normal circumstances.
However, the editing environment in my application is far from normal. Users edit the data by accessing the entire table; it's almost as if the table is treated like big spreadsheet or something (which is kind of frightening when I think about it). Anyway, the scenario is called "editing in the attribute table window". Users can manually edit any column in any row, which means they can override the IDs that are generated by the sequence and trigger.
- A user creates a new row. The sequence and trigger provide an ID of
- A user then goes and manually changes
LC000007. (I can't think of a good reason why a user would do this, but they have the ability, which means it will probably happen.)
A user creates another new row. The sequence and trigger attempt to provide an ID of
LC000007, but it throws an error:
Database Row Change: An unexpected failure occured. Underlying DBMS error [ORA-00001: unique constraint (USER1.PROJECT_ID_IDX) violated][USER1.PROJECTS]
The error is caused by the unique index on the field. There are can't be duplicate values.
Practically speaking, I could live with the fact that my users would get an error like this (if someone on the team was unwise enough to manually override one or two
PROJECT_IDs). All users need to do is try again, and the sequence/trigger just magically skips to the next number.
But of course, there's the possibility that there are 10's or 100's of IDs that are out-of-sequence (not just one or two). Believe it or not, this is a real possibility, due to the existence of a tool called the field calculator. With this in mind, getting an error message for 10's or 100's of out-of-sequence IDs doesn't seem so practical.
So, I'm curious, can I avoid this problem somehow?