Oracle – Handling Manual Overrides in Auto-Incremented ID with Sequence and Trigger

oraclesequencetriggerunique-constraint

I have a sequence and trigger that provide auto-incrementing IDs to a PROJECT_ID column:

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;

^{Source: Trigger to get next prefixed ID: Is there a better way?}

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.

For example:

A user creates a new row. The sequence and trigger provide an ID of LC000006.
A user then goes and manually changes LC000001 to 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?

Best Answer

Instead of trying to address what happens after someone updates the PROJECT_ID column, have you tried denying updates of the PROJECT_ID column in the first place?

Perhaps revoking update privileges on the PROJECT_ID column? Or perhaps ... yuck ... a trigger to rollback (and generate an error) any attempts to update the PROJECT_ID column?

Since you're ok with them getting an error when they attempt to generate a duplicate PROJECT_ID, I'm assuming you'd be ok with them getting an error when attempting to update PROJECT_ID ... and after a couple such attempts ... and perhaps a "WTF!?!?!" email to the DBA (you?) ... they'll get used to the fact that they can't update the PROJECT_ID column.

Or do you really want to allow them to update the PROJECT_ID column?

Related Solutions

Mysql – How to modify this trigger with loops and concat

How about expanding this a little and do a proper log table, logging every update, insert or delete. That way you can keep a proper history. For this, in MySQL, you will need three triggers :

CREATE TABLE EMP ( ID INT,
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TABLE EMPLOG ( empid INT,
                   operation varchar(1),
                   operationdate date,
                   operated_by varchar(200),
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TRIGGER emplog_insert AFTER INSERT ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(NEW.id,
 'I',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_update AFTER UPDATE ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(NEW.id,
 'U',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_delete AFTER DELETE ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(OLD.id,
 'D',
 Now(),
 USER(),
 OLD.lastname,
 OLD.firstname,
 OLD.gender,
 OLD.dob,
 OLD.marital,
 OLD.SSN
);

If you then wish to check out the history of what happened for a specific employee, you simply run this query :

select *
from Emplog
where empid = ?
order by operationdate

Mysql – Converting a composite primary key to composite unique, and adding an auto-incremented PK column

That smells a lot like a "many-to-many" relationship table. Is it?

As for linking from a changelog -- space is not the issue; correctness is. A change log should have exactly what existed at the time the log was taken. What if the original table is updated? If the table in question is changed for any reason, I think you do not want the change reflected in the existing changelog entry. (Though, perhaps in a new changelog entry.)

If you would like to discuss your particular case further, please provide SHOW CREATE TABLE and a few queries that use the table.

Here's another tip: It is OK to do the following:

id INT UNSIGNED AUTO_INCREMENT,
...
PRIMARY KEY (...),
INDEX(id)

That keeps the original PK clustered, which might be beneficial, yet still allows for an AI key.

Another tip: Don't say UNIQUE(foo) in place of INDEX(foo) unless there is some reason for doing the uniqueness constraint. (Example: INSERT ... ON DUPLICATE KEY ...)

Further discussion about many-to-many.

Best Answer

Related Solutions

Mysql – How to modify this trigger with loops and concat

Mysql – Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Related Question