Best strategy question

[Ivan Smahin]

Hello Bart,

Thursday, September 11, 2008, 9:08:13 PM, you wrote:

> Hi guys,

> bit of a strategy question here:

> what is the best way (inside a stored procedure) to verify if a record
> exists. If it does not exist I want to insert it, if it exists not.
> The table I will insert the record has a unique_check method in place

> So what do I do:

> 1) immediately insert the record and count on the exception to tell me
> it already exist
The cons are -
1. some triggers might be fired (before insert)
2. there are some work for processing fields with new values (it could be a lot of
fields) and that work might become redundant in case of exception.

The pros is - you define unique constraint - so it is good in schema
definition meaning


> 2) do a select on the record to verify if it exists on only do the  
> insert if the cursor returned empty

I would prefer this way - especially in SPs programming. SP is part
of the business logic - there is no reason to expect some unique
violation instead of just checking that uniqueness

> I am inclined to think 1) is optimal for speed but is it the best way
> altogether....

Personally I would create unique constraint but check uniqueness
before insert attempt.

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com