Crashes during record insertions

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)

Crashes during record insertions

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-09 : 11:08:27

Hello,

My question is:

Assume there are 2 tables, A and B.

Table A has a record inserted into it.
Table B should have a corresponding record inserted into it but
after the record from table A is inserted, the program crashes.

I would not want the record in table A to be inserted if the record
in table B was not.

Sorry I am new to this.

thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-09 : 23:24:15

The quick answer is to use Transactions.

Look in SQL Server books online for more details, or do a search on 'Transactions' on this site.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 08:15:48

BEGIN TRANSACTION tran_wrap

DECLARE @scope_identity INT

INSERT table1(record)
SELECT record

SELECT @scope_identity = SCOPE_IDENTITY()

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION tran_wrap
END

INSERT table2(record)
SELECT @scope_identity

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION tran_wrap
END

COMMIT TRANSACTION tran_wrap

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-10 : 18:07:08

Thanks I will look into transactions and thanks for the code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources