Solved

How to avoid dup keys with trigger and sequence

Posted on 2007-12-04
8
770 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
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 250 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Required to Import Oracle Database Table 3 43
select query - oracle 16 81
oracle query help 36 66
sql query 9 21
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now