Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

How to avoid dup keys with trigger and sequence

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
jvv
Asked:
jvv
1 Solution
 
ajexpertCommented:
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
 
sujith80Commented:
>> 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
 
jvvAuthor Commented:
>>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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
jvvAuthor Commented:
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
 
jvvAuthor Commented:
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
 
sujith80Commented:
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
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now