We help IT Professionals succeed at work.
Get Started

possible use of triggers to aid prevent inserts of duplicates

andieje
andieje asked
on
439 Views
Last Modified: 2012-05-11
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
Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE