When should I to wrap DML to transaction

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 perfor
m
update i exatly know that only one row will be updated , is necessary or whe
n
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 wra
p
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

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