?
Solved

Trigger or Procedure to detect duplicate record and resume next.

Posted on 2003-03-13
1
Medium Priority
?
514 Views
Last Modified: 2012-06-27
I am currently doing a migration from MS Access database to Oracle database table. The migration process is automated by running an executable. The problem is that the records in the old database table contains duplicate entries. So how do I write a trigger that will compare the records in the new Oracle database table with the one that will be inserted that will fire before inserting. To my knowledge from my seniors, once a duplicate record is detected, the whole migration process will be stopped. What do i need to add into the trigger so that once a duplicate record is detected it will not insert it into the new table and resume to the next record to be migrated instead of stopping the whole process.
0
Comment
Question by:vyruzj
[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
1 Comment
 
LVL 2

Accepted Solution

by:
alligatorsql earned 225 total points
ID: 8134830
Hello,

that is not the whole trues.

1) When you insert a duplicate record into Oracle,  nothing will happen.
2) When you have definied a primary key or a unique key on the Oracle table, you will get a key constraint violation error and an exception will be raised.

So ... if you use only INSERT INTO TABLE ....
your procedure stops, cause the exeception is not caught by your procedure.
One way to catch the exception is

BEGIN
    INSERT INTO TABLE ...
EXCEPTION
    WHEN OTHERS THEN
       ... do your error handling
END;

In this way, the procedure do not stop and you can write your duplicate records into a separate table or something like that.

3) If you want to use a trigger to detect duplicate values you have to use the insert before row trigger ...
Here is a short example

4) If you use the insert before row trigger, it could be, that you run into a "mutating table problem" which means, that you do operations on a table that is manipulated by the same process.
If you have such problems ... do the following

4a) Create a statement before trigger and move all your insert datas into a PL/SQL record.
4b) Create a insert before row trigger and check the dupicate values. If duplicate values exits throw an exception.
4c) Create a statement after trigger and insert the record out of your PL/SQL record.

That works fine !!!

5) If you only wants to filter the duplicate rows, you only have to create a primary key on the table or a unique key. If you insert a record that violates that key, oracle throws an error.

Hope that helps ... otherwise let me know

Best regards
Manfred Peter
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 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