Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
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
 
Old May 28th, 2004, 03:40 AM
Registered User
 
Join Date: Apr 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old May 28th, 2004, 05:14 AM
Registered User
 
Join Date: Apr 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old May 28th, 2004, 02:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 6th, 2004, 05:34 AM
Authorized User
 
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 6th, 2004, 05:40 AM
Authorized User
 
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:





Similar Threads
Thread Thread Starter Forum Replies Last Post
Transaction-urgent mikedeepak Classic ASP Professional 0 December 13th, 2005 10:03 AM
Transaction Management azmatazz Biztalk 0 April 28th, 2005 07:17 AM
subform transaction elleetan Access 0 September 18th, 2003 02:18 AM
Transaction Management sonaidg Java Databases 2 August 19th, 2003 04:20 AM
how to use Transaction ? locka ADO.NET 0 August 12th, 2003 08:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.