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 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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200508/1