When should I to wrap DML to transaction

[akej via SQLMonster.com]

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/Forums...neral/200508/1

[Simon Hayes]

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