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