Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rliss
Starting Member
31 Posts |
 Posted - 2006-05-16 : 11:33:17
|
| I've already read documentation on @@error and TRY..CATCH...Here's the problem:In my trigger, I have an insert statement. If the insert fails because the record already exists, I would like to update it and go on. In both methods in my opening statement, the transaction was aborted.In Oracle, using a RETURN statement in the exception block will return control to the next line after the exception was thrown. How can that be achieved in SQL Server?RLiss |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-05-16 : 13:18:42
|
| a constraint violation will halt execution of your code and abort transactions which is why neither of your error handling techneques worked.Typically an insert in a trigger will be for things that won't violate constraint violations. Usually you are inserting to an audit table or a process queue, etc. The reason is because of the same problem you are haveing, you don't want your original transaction aborted. One way to achieve the update/insert thing is to first do the update, then check the @@rowcount value. If zero then do the insert. The problem here is that the higher the concurrency the more likely you will have collisions between the update and insert with concurrent actions. You can include a WHERE NOT EXISTS clause in your insert statement. Then you could check @@rowcount again and if it is still zero you can decide if you want to rollback or just keep going.Be One with the OptimizerTG |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2006-05-16 : 14:14:02
|
| I usually do something like this:IF EXISTS (SELECT .... that checks if the record exists) UPDATE ...ELSE INSERT===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-17 : 06:10:11
|
| orUpdate........If @@RowCount=0Insert........MadhivananFailing to plan is Planning to fail |
|
|
|
Rliss
Starting Member
31 Posts |
Posted - 2006-05-17 : 12:30:47
|
| I ended up going with the IF EXISTS, and I'm on my way. Thanks to all...RLiss |
|
|
|
|
|
|
Exception Handling