begin tran - 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

SQL Server Development (2000)

begin tran

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-04-25 : 22:14:30

ok I have a transaction that looks like this...its just in query analyzer for now...

begin tran
delete from tableA
alter tableB drop FK
delete from tableB
alter tableB add FK

problem I have is that the alter tableB statement to add the FK pukes cause the constraint is missing id's which is ok ....but if I go to ROLLBACK tran I get an error stating there was no transaction started...why is that...I have a begin tran

in fact if I leave out that alter add statement and run the 2 deletes and alter drop fk ....I can rollback the tran....is that cause it didn't error out...and when it error out it rollback automatically??

mattyblah
Starting Member

49 Posts

Posted - 2007-04-26 : 00:24:27

try this, put the following in a stored proc:


create procedure usp_testproc
as

begin tran
delete from tableA
if @@error <> 0
begin
rollback tran
return -1
end
alter tableB drop FK
if @@error <> 0
begin
rollback tran
return -1
end
delete from tableB
if @@error <> 0
begin
rollback tran
return -1
end
alter tableB add FK
if @@error <> 0
begin
rollback tran
return -1
end
commit tran

what happens when you run:


exec usp_testproc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources