 |
| SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
May 28th, 2004, 03:40 AM
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Help required regarding transaction management
Dear friends,
i have a stored procedure where i want to run many queries,and if any query fails
then i want to rollback all previous quries, i write some code infact i want verification that
is this is the right way and suggestions also
regards
alter procedure TranTest
as
BEGIN
BEGIN TRAN T1
insert into table1(id,name) select 1,'t1'
If @@Error<>0
begin
rollback tran T1
return
end
BEGIN TRAN T2
insert into table1(id,name) select 2,'t2'
If @@Error<>0
begin
rollback tran T1
rollback tran T2
return
end
BEGIN TRAN T3
insert into table1(id,name) select 1,'t2'
--select * from table1
If @@Error<>0
begin
rollback tran T1
rollback tran T2
rollback tran T3
return
end
--at end
commit tran T1
commit tran T2
commit tran T3
return
END
|
|
May 28th, 2004, 05:14 AM
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
dear friends i got another way... suggest pls below is a code
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
SET XACT_ABORT on
BEGIN TRAN
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
SELECT * FROM t2
GO
SELECT * FROM t1
GO
DROP TABLE t2
DROP TABLE t1
GO
|
|
May 28th, 2004, 02:01 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
|
|
When you say you want to rollback all your previous transactions when any of the inserts fails due to some reason, you dont have to rollback them by name to the previous checkpoint. You can blindly give ROLLBACK TRAN alone.
I have edited your procedure below.
Quote:
quote:alter procedure TranTest
as
BEGIN
BEGIN TRAN
insert into table1(id,name) select 1,'t1'
If @@Error<>0
begin
rollback tran
return
end
insert into table1(id,name) select 2,'t2'
If @@Error<>0
begin
rollback tran
return
end
insert into table1(id,name) select 1,'t2'
--select * from table1
If @@Error<>0
begin
rollback tran
return
end
--at end
commit tran
return
END
|
Hope that helps
_________________________
-Vijay G
 Strive for Perfection
|
|
June 6th, 2004, 05:34 AM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I don't think you need 3 transactions for this. Only one would do it, like this:
alter proc TranTest
as
begin tran
insert into table1 (id, name) select 1, 't1'
if @@error <> 0
begin
rollback tran
return
end
insert into table1 (id, name) select 2,'t2'
if @@error <> 0
begin
rollback tran
return
end
insert into table1 (id, name) select 1,'t2'
--select * from table1
if @@error<>0
begin
rollback tran
return
end
commit tran
|
|
June 6th, 2004, 05:40 AM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
or a shorter but more "unprofessional" version below (unprofessional, because of the goto usage):
alter proc TranTest
as
begin tran
insert into table1 (id, name) select 1, 't1'
if @@error <> 0 goto err
insert into table1 (id, name) select 2,'t2'
if @@error <> 0 goto err
insert into table1 (id, name) select 1,'t2'
if @@error <> 0 goto err
commit tran
goto theend
err:
rollback tran
theend:
|
|
|
