Transaction problem - 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

General SQL Server Forums

New to SQL Server Programming

Transaction problem

Author

Topic

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-03-06 : 07:34:12

I have a transaction that I need to save some data from to a table even if it gets rollbacked. If you can modify the simple code below to work and get the int 4 saved into the table but not the int 1 I would really appreciate it!! Thanks in advance!

CREATE TABLE testing(
test INT
)

BEGIN TRANSACTION test1
INSERT INTO testing SELECT 1

BEGIN TRANSACTION test2 --WITH Mark 'Testing'
INSERT INTO testing SELECT 4
COMMIT TRANSACTION test2

ROLLBACK TRANSACTION test1

SELECT * FROM testing

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-03-06 : 07:34:45

quote:
Originally posted by IceDread

I have a transaction that I need to save some data from to a table even if it gets rollbacked. If you can modify the simple code below to work and get the int 4 saved into the table but not the int 1 I would really appreciate it!! Thanks in advance!

CREATE TABLE testing(
test INT
)

BEGIN TRANSACTION test1
INSERT INTO testing SELECT 1

BEGIN TRANSACTION test2
INSERT INTO testing SELECT 4
COMMIT TRANSACTION test2

ROLLBACK TRANSACTION test1

SELECT * FROM testing

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-03-06 : 07:36:00

Darn could not find edit. wanted to remove --WITH Mark 'Testing' so no talk about it would occur.

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-03-13 : 07:52:46

Noone knows how to do it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-13 : 08:01:28

If you want to save some data irrespective of transaction failure, then that INSERT should not be part of transaction in the first place, isn't it?

CREATE TABLE testing(
test INT
)

INSERT INTO testing SELECT 4

BEGIN TRANSACTION
INSERT INTO testing SELECT 1

If @@Error <> 0
Begin
 ROLLBACK TRANSACTION
 -- Error Processing
End

COMMIT TRANSACTION

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources