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: 525
  • Last Modified:

Trigger or Procedure to detect duplicate record and resume next.

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
vyruzj
Asked:
vyruzj
1 Solution
 
alligatorsqlCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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