|
Home > Archive > Microsoft SQL Server forum > August 2005 > When should I to wrap DML to transaction
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
When should I to wrap DML to transaction
|
|
| akej via SQLMonster.com 2005-08-22, 3:23 am |
| Hi to all.
Many times i saw that some people wrap the single insert, delete or update
statements to transaction.
My question is suppose i have procedure , and inside this procedure i perform
update i exatly know that only one row will be updated , is necessary or when
i need to wrap it with transaction
------------------------------------------------------------------------------
CREATE PROCEDURE Test_sp
@ID int
as
UPDATE MyTable
SET col1 = 'bla bla'
WHERE [ID] = @ID
------------------------------------------------------------------------------
-
in code above only on row will be updated so why some times i see :
------------------------------------------------------------------------------
CREATE PROCEDURE Test_sp
@ID int
as
BEGIN TRAN
UPDATE MyTable
SET col1 = 'bla bla'
WHERE [ID] = @ID
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
IF @error = 0 AND @rowcount =1
GOTO SUCS_STEP
ELSE
ROLLBACK TRANSACTION TRC_EVENT
GOTO ERROR_STEP
END
ERROR_STEP: RETURN 0
SUCS_STEP:
COMMIT TRANSACTION TRC_EVENT
RETURN 1
GO
------------------------------------------------------------------------------
-
The update statement is itself in transaction so why there are need to wrap
to tranasction.
So PLZ explain in wich cases i need to wrap (INSERT, UPDATE, DELETE)
statements into
transactions????
ONLY when i perform a single statement i.e one insert or one delete or one
update !!!!
TNX.
--
"Imagination is more important than knolwege" (Albert Einshtein)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200508/1
| |
| Simon Hayes 2005-08-22, 7:24 am |
| Without BEGIN TRAN, you can't do a ROLLBACK if something unexpected
happens. In your code sample above, the developer expects that the
UPDATE will affect only one row, but if that isn't what happens, he
wants to rollback the UPDATE - perhaps two rows were affected, perhaps
there was another error of some kind.
You might find this article useful for general information about error
handling:
http://www.sommarskog.se/error-handling-II.html
Simon
|
|
|
|
|