Back in June I discussed how Oracle databases don’t have an AUTO_INCREMENT type. In the process of this discussion I showed a way around this. While I like this method in certain situations, in others it has several flaws that really bug me. First off and foremost if you are using your table as a log tracking the progress of a process than you’ll have to reset the sequence every time you restart the process. Well, I guess that assumes you, like myself, truncate or delete * from that table every time you run the process. Either way, I think that should be an unnecessary step. It’s sort of like having an UPDATED_ON column and actually feeding it the SYSDATE every time you do an insert. Why not just set DEFAULT SYSDATE when you create the table?
Now my second method for replacing the AUTO_INCREMENT type has it’s flaws as well, but it works quite well for the log style table I described above. This time we won’t use a SEQUENCE at all though. Instead we’ll simply use the rowcount(*).
CREATE OR REPLACE TRIGGER state_trg
FOR EACH ROW
SELECT rowcount(*)+1 INTO :NEW.column_pk FROM state_table;
Now before you go crazy and start adding this to all kinds of tables be careful, this method isn’t perfect either. First if you ever delete from the table without truncating or deleting * you’ll (hopefully) get a unique constraint violation error. Hopefully why will be obvious. Again, the code has it’s uses, it’s up to you decide when and where it’s a good or bad idea.
Enjoy the Penguins!