Solved

How to avoid dup keys with trigger and sequence

Posted on 2007-12-04
8
777 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Action link in Union Reports Not Working in OBIEE 11g 1 89
Oracle DATE Column Space 11 80
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 49
Help on model clause 5 32
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

832 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