?
Solved

DB trigger to delete duplicate rows

Posted on 2004-11-29
4
Medium Priority
?
1,345 Views
Last Modified: 2013-12-11
I want to write a DB trigger. I dont have any exp with oracle.
I have two tables - profile and product
The issue I have is that for each row in table profile, there are atleast 4 identical rows in table product. I want to clean up table product so that for each profile there is only one product profile.
HOw to do this?
I want to have a DB trigger to check everytime theres an insert on table product...that is to delete duplicate rows.
Thanks in advance
0
Comment
Question by:Vasi04
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12694188
Why do you not want to clean up your product table in one operation ?
Is there a reason you want to use a trigger ?
0
 

Author Comment

by:Vasi04
ID: 12694199
I want to use trigger to avoid future insert of duplicate rows. Is this spossible?
With Single operation I can get rid of already existing duplicate rows.


0
 
LVL 7

Accepted Solution

by:
Nievergelt earned 500 total points
ID: 12696514
You should be able to that with a trigger like the following:

CREATE OR REPLACE TRIGGER avoidDuplicates
BEFORE INSERT ON product
  DELETE product WHERE <key conditions>

<key conditions> is the expression that define an "identical" entry where you reference values in the entry to be inserted with new (e.g. new.productId).

While this works, it is not a good idea, if you have many users working on the database.
0
 
LVL 5

Expert Comment

by:sora
ID: 12761892

The best way to do this is to create a Primary key on the product profile. If the table already has a product profile, then create a unique index on product profile. This will ensure that duplicates do not occur.

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

809 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