Inserting a new record 2

[TiltingCode]

Question: When inserting a new record, should I first do a Select statement to make sure the record doesn't already exist? Or should I write the code to warn the user if the Insert statement comes back with an error.

I wrote code for the former but another programmer said to do the later.

What do you all suggest?

 

[JoeAtWork]

Between the two choices I prefer the SELECT statement because you can tell the user exactly what the problem is. The latter just tells the user there was "some error" and you can't just assume the only error that might happen is a duplicate record (i.e. don't show the user a "duplicate record exists" error just because the INSERT failed).

A better solution would be to write a stored procedure to add records, and within the stored procedure you could do your SELECT statement and if a duplicate doesn't exist proceed with the insert. You could have a specific return value to indicate "duplicate record found".

 

[SQLScholar]

TiltingCode,

The other option is to write a trigger on insert to check before you do the insert. You can then do whatever you want to do if there is one already. You could even write to a log table to keep a track of how often it happens.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[markros]

I suggest the second approach. You may try trapping the error either in SQL Server and pass it to the calling application or directly in the calling application.

PluralSight Learning Library

 

[TiltingCode]

Thanks for your suggestions... The trigger is pretty interesting.