issues with 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)

issues with transaction

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-13 : 17:39:35

Below statement is part of a trigger.I want to make all of them in a single transaction and if it fails it should roll back.what code should I use

IF user=@user

BEGIN

EXECUTE usp_emp_details

EXECUTE usp_task_assigned_details

EXECUTE usp_credit_details

EXECUTE usp_approaches_Lt_details

EXECUTE usp_secondary_details

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 17:46:16

You need to use BEGIN TRAN and test the value of @@ERROR after each DML statement. If @@ERROR<>0, then ROLLBACK TRAN. At the end if @@ERROR was always equal to 0, then COMMIT TRAN.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-13 : 17:51:56

Can I use it under one transaction

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 17:54:55

Yes.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-13 : 18:08:21

This will give my result properly and if any error it will roll back right... tara

Begin Tran

IF user=@user

BEGIN

EXECUTE usp_emp_details

EXECUTE usp_task_assigned_details

EXECUTE usp_credit_details

EXECUTE usp_approaches_Lt_details

EXECUTE usp_secondary_details

END

IF @error<> 0 then
ROLLBACK TRAN
ELSE
COMMIT TRAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 18:13:22

No. You must check the value of @@ERROR after every single DML statement.

Here's an example:



DECLARE @err int

BEGIN TRAN

INSERT INTO Table1... 

SET @err = @@ERROR

IF @err <> 0
	GOTO ERROR

INSERT INTO Table2...
			
SET @err = @@ERROR

IF @err <> 0
	GOTO ERROR

UPDATE Table3... 

SET @err = @@ERROR

IF @err <> 0
	GOTO ERROR
ELSE
	COMMIT TRAN

ERROR:

ROLLBACK TRAN

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources