Syntax of transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax of transaction

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2006-06-24 : 04:40:02

@LeagueID int,
@LeagueName varchar(50),
@LeagueAbbr varchar(50),
@LeagueSport varchar(50),
@LeagueState varchar(50),
@LeagueURL varchar(50),
@LeaguePointsforawin decimal(5),
@LeaguePointsforatie decimal(5),
@LeaguePointsforaloss decimal(5),
@SendEmailOnAssign bit,
@SendEmailOnAdd bit,
@LeaguePublic bit,
@LMID as int,
@Date as datetime
AS
I have written a query in a transaction which can update two tables
begin tran update_leagues, which is given below

update Leagues set
LeagueName=@LeagueName,LeagueAbbr=@LeagueAbbr,LeagueSport=@LeagueSport,LeagueState=@LeagueState,LeagueURL=@LeagueURL ,LeaguePointsforawin=@LeaguePointsforawin ,LeaguePointsforatie=@LeaguePointsforatie,LeaguePointsforaloss=@LeaguePointsforaloss,LeagueSendEmailUponAssign=@SendEmailOnAssign ,LeagueSendEmailUponAdd=@SendEmailOnAdd ,LeaguePublic=@LeaguePublic
where LeagueID=@LeagueID

update assLeagueLeagueManagers Set llmDateJoined = @Date
Where llmLeagueID = @LeagueID and llmLeagueManagerID = @LMID

commit tran
if (@@error <> 0 )
rollback tran update_leagues

tell me whether this syntax of transaction is true or not ?
Will this query works properly for transaction ?...will it rollback transation on error or commit with no error ?

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-24 : 06:04:12

You should put transaction block after each transaction statement, so that it is rolled back when it fails.

This link will help you some how.
http://www.informit.com/articles/article.asp?p=26657&rl=1

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2006-06-24 : 07:37:56

You code, in essence, with line numbers:
01 begin tran update_leagues
02 update Leagues ...
03 update assLeagueLeagueManagers ...
04 commit tran
05 if (@@error <> 0 )
06 rollback tran update_leagues

01 Starts the transaction

02 Performs first update. There is NO check of @@ERROR, so if this statement fails you will not trap any error.

03 Ditto. Worse, if (02) fails (e.g. deadlock error) the transaction block will be cancelled and this update will run OUTSIDE the transaction block

04 The transaction is committed. So whatever has happened above it is now committed. If you got, say, a deadlock error in (02) and then (03) then ran outside the transaction block you will now get an error (transaction level was 0, so no transaction to commit)

05 Checking @@ERROR here does no good. The scope of @@ERROR is only the immediately preceding statement (its not like, say, VB where the error number is retained until explicitly cleared). So you have to check @@ERROR after EVERY statement - see Books OnLine for more details.

06 This would be fine it used in combination with (05) but it needs to be in the right place!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 02:25:25

http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources