January 28, 2004 at 7:26 pm
Is there any way to combine UPDATE and INSERT statement as a single statement. I need the insert statement to be executed only if the update statement is excuted. If the insert statement fails, i want to rollback the same. I have tried COMMIT, ROLLBACK , but still i get partial updates and bad inserts. This happens once in a while. This use VB6 and SQL Server 2000. Please advice
January 29, 2004 at 12:38 am
Yes, you must use an explicit transaction for this.
BEGIN TRAN
UPDATE myTab set col1 = .... etc..
-- check for errors
if (@err 0) goto errhandler
-- now do the insert
INSERT otherTab ....
-- check for errors
if (@err 0) goto errhandler
-- if we have come this far, we're good.
COMMIT TRANSACTION
return
errhandler:
--when we get an error we end up here
if (@@TRANCOUNT > 0) ROLLBACK
return
The above is greatly simplified, but it shows the principle.
If you have triggers and stuff involved, the code must take that into account too, else you may get unexpected results.
I strongly advide to read up on transactions in BOL (Books on Line) - it's a complex subject, and it's critical that you understand it fully.
=;o)
/Kenneth
January 29, 2004 at 12:52 am
Oh, just another thing about explicit transactions...
Do NOTinitiate this kind of code from the client!
Everything from and including 'BEGIN TRAN' up to 'COMMIT' should be inside a stored proc on the server,
letting the server handle the entire transaction.
If you initiate transactions, or issue this kind of statements from the client or a middle tier,
you're just waiting for the system to grind to a halt due to abandoned and/or open transactions.
=;o)
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply