sql server - Is it necessary to use a transaction if other statements are dependent on the first? -

for long, i've omitted using sql transactions, out of ignorance.

but let's have procedure this:

create procedure createperson  begin  declare @newperson int  insert persontable ( columns... ) values ( @parameters... ) set @newperson = scope_identity()  insert anothertable ( @personid, createdon ) values ( @newperson, getdate() )  end go 

in above example, second insert depends on first, in fail if first 1 fails.

secondly, , whatever reason, transactions confusing me far proper implementation. see 1 example here, there, , opened adventureworks find example try, catch, rollback, etc.

i'm not logging errors. should use transaction here? worth it?

if so, how should implemented? based on examples i've seen:

create proceure createperson  begin transaction  ....  commit transaction go 

or:

create procedure createperson  begin     begin transaction      commit transaction end go 

or:

create procedure createperson  begin  begin try     begin transaction      ...      commit transaction end try begin catch     if @@trancount > 0     begin         rollback transaction     end end catch end 

lastly, in real code, have more 5 separate inserts based on newly generated id person. if me, do? question perhaps redundant or duplicate, whatever reason can't seem reconcile in mind best way handle this.

another area of confusion rollback. if transaction must committed single unit of operation, happens if don't use rollback? or rollback needed in try/catch similar vb.net/c# error handling?

you missing point of this: transactions suppose make set of separate actions one, if 1 fails, can rollback , database stay if nothing happened.

this easier see if, let's say, saving details of purchase in store. save data of customer (like name or address), somehow in between, missed details (server crash). know john doe bought something, don't know what. data integrity @ stake.

your third sample code correct if want handle transactions in sp. return error, can try:

return @@error 

after rollback. also, please review about:

set xact_abort on 

as in: sql server - transactions roll on error?