?
Solved

How to avoid dup keys with trigger and sequence

Posted on 2007-12-04
8
Medium Priority
?
781 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

752 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