troubleshooting Question

possible use of triggers to aid prevent inserts of duplicates

Avatar of andieje
andieje asked on
MySQL Server
3 Comments1 Solution440 ViewsLast Modified:
Hello

I am regularly faced with loading large input files into a database and checking to see whether the data already exists in the database before inserting new records. The input data is split across multiple tables in the database.

The insert is performed in a perl script. I normally perform a select command to look to see if the data exists before inserting a new record but I was wondering if  i should use a before insert trigger on the database to check if the record exists and stop the insert that way

These threads discuss this issue from different viewpoints
http://forums.mysql.com/read.php?99,386598,386598
http://stackoverflow.com/questions/408749/enforce-unique-values-across-two-tables

Question 1
Would this be quicker as the select command to look for the record is issued by a trigger rather than a perl statement or are triggers slow

Question 2
How would i stop the insert in the trigger

Question 3
If the trigger performs a select command to look for the record being inserted and it finds the record, can it pass the id of the record back to the perl statement which issued the insert

thanks

thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros