Relying on Insert Failure For Update Statement - Good Idea or Not?

Question

Relying on Insert Failure For Update Statement - Good Idea or Not?

M Chabot

SSCrazy

Points: 2511
More actions
March 24, 2005 at 9:37 am

#66994

Two ways to determine whether to do an insert or an update are:
1. Query to see if the record exists, then perform the appropriate action, or
2. Try the insert, then do the update if the insert fails.
In the scenario I am dealing with, the form should only ever be submitted once, and there should not be any updates to the table. However, the person can press back in their Web browsers, and submit the form again with altered values for the rare occasion that they made a mistake. I am leaning towards the second option to determine which action I need to perform. What do other people think?
Thank you,
Mike Chabot

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

I'm against the idea. Relying on an error to decide on an insert vs update just doesn't seem like the way to do it.

The cost to do a query and the extra lines of code are worth more to me to have a smooth running procedure/function/trigger than to rely on an error.

The code would be:

SELECT idnum FROM tablename
where idnum = newid

IF @@RowCount > 0
    %UPDATE QUERY%
ELSE
    %INSERT QUERY%

And that would also solve your other post about coldfusion returning an error.

But that is my $0.02. You are the programmer on the spot. And when someone else follows your code later on and gets don't say someone didn't tell you.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

hoo-t SSChampion Points: 12713 More actions · Answer 2

Well, given the choices, I like AJ's answer (in the other thread). But, how about option 3?

With the scenario you have given, the user is still filling the form, and may hit the back button to change a previous answer. This indicates to me that the form hasn't been completed yet, and therefore the TRANSACTION hasn't completed yet. I would store all the values in local variables until the user hits the "Submit" button (or what ever you have), and then, and only then insert the data into the database. At that point, if the record already exists, it is truly a user error, and an appropriate message can be displayed.

Steve

[edit] : By the way, Jim, I didn't mean to exclude you, your method is just as valid.

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 3

While I agree the Select provides a cleaner approach.

I've always used a percentage based approach.

If I figure the the majority will be ok, I do an insert and check for the error. I figure I would have to get at least 70 %, really 80 or 90 %, inserting without an error before I'd use that approach.

If I figure the majority will be there when doing the insert, I Select then ...

But back to reality on this specific question, hoo-t has a point about mid point transaction. That is really the issue here.

KlK

M Chabot SSCrazy Points: 2511 More actions · Answer 4

A third option I failed to mention:

3. Perform the update, if zero rows are affected, perform the insert.

Relying on error handling for a common condition makes me a bit uneasy, so I will go with the option of checking for existence first. However, in the scenario I presented, with 99% inserts and 1% updates, relying on the error condition seemed to be the most efficient solution. I wonder how many people view error handling as just a special type of conditional statement? IF condition1 = error THEN process2.

I do not believe I can hold a database transaction open across Web pages with ColdFusion. Once the user moves on to another page, all transactions related to that first page are finalized. The user could close their Web browser after submitting the form.

Thank you,

Mike Chabot

noeld SSC Guru Points: 96590 More actions · Answer 5

I have discuss this issue too many times with friends and coworkers but it seems like an endless loop

The method for checking existence first is not valid in a heavy transactional enviroment.

Supposed two people are trying to use the procedures with the same parameters. When both get the exists negative anwser both are going to try an insert and only one of them is going to be successful.

Threfore the guidelines are:

1. Like (Steve proposed) make sure the call is issued only when there is nothing else to do. In other words: just once.

2. Should you need to mix insert with updates in a single sp the go with the two commands without checking extistence in a separate query or if statement.

3. Try (if you can) to determine before hand if what you need is one or the other and separate the actions in different procs (this is safest, fastest and more reliable way)

hth

* Noel