Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to avoid dup keys with trigger and sequence

Posted on 2007-12-04
8
Medium Priority
?
782 Views
Last Modified: 2013-12-18
I have an autoincrement id column in my table by using a sequence and an update triggers. Working okay so far.
Assume we already have 10 records, and the sequence value is 10 as well. Then, when the next record is inserted the sequence will be incremented by 1 and the id column will be 11, okay.
But, I want that trigger to be able to handle situations where a duplicate key should occur.
Assume now that - for some reason - we already have a record with id 12, then the next insert will cause a duplicate key error.
How can I change the trigger to repeat the sequence.nextval until it finds a situation where it can safely insert the record?
create trigger trg_autonumber
before insert on tbl_autonumber
for each row
begin
  select seq_autonumber.nextval into :new.id from dual;
end;
/

Open in new window

0
Comment
Question by:jvv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 20407932
Hi,
Can you change the query in trigger like the following
select MAX(ID) + 1  into :new.id from tbl_autonumber;

This will ensure always unique records.

Hope this helps
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 20408673
>> How can I change the trigger to repeat the sequence.nextval until it finds a situation where it can safely insert the record?

A trigger is not the best solution for this scenario.
-- Simplest thing you can do is - to re-create the sequence with a value greater than the max value of ID in your table. And then make sure that the trigger is enabled on your table. So, there will never be a chance of having duplicate ids in the table.

See whether this solves the issue.
0
 

Author Comment

by:jvv
ID: 20410413
>>select MAX(ID) + 1  into :new.id from tbl_autonumber;
what is the impact on the performance?
and is MAX(ID)= 0 if the table is empty?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jvv
ID: 20410704
I made this trigger. This solves all the issues in my opinion.
My only worry is if it will cost more performance than doing it with a sequence.
What is your opinion?

CREATE OR REPLACE TRIGGER testtrg
   BEFORE INSERT ON testtbl
   FOR EACH ROW
BEGIN
   SELECT NVL (:NEW.ID, NVL (MAX (ID) + 1, 1))
     INTO :NEW.ID FROM testtbl;
END;

Open in new window

0
 

Author Comment

by:jvv
ID: 20410876
insert into testtbl (col2) select 'test' from dual

gives this error

ORA-04091: table TESTTBL is mutating, trigger/function may not see it
ORA-06512: at "TESTTRG", line 2
ORA-04088: error during execution of trigger 'TESTTRG'
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20411405
I have already mentioned that a trigger is not the best solution for your case.
In a rowlevel trigger you cannot query from the same table.This will lead to a mutating table error.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20936928
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question