Question about Transactions - 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

Transact-SQL (2000)

Question about Transactions

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-12-04 : 17:55:16

Hi,

I have two insert commands in the transactions something like this:

BEGIN TRAN

INSERT INTO Customers(FirstName, LastName)
VALUES('John','Doe')

// PhoneNumber gets inserted into the Phones table
INSERT INTO Phones(PhoneNumber)
VALUES('6666')

COMMIT TRAN

ABORT_InsertCustomers:
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END

The insertion in the Customers table will throw an exception since I am not including the email column. Now, I want that if the Customer is not inserted then the Phone should also not be inserted even though there is no relationship between the Customers table and the Phones table.

I know I can use @@ERROR after every INSERT but I think there should be some easier way. Another, way is to create a relationship between the two but suppose I am inserting two things which does not have relationship.

Any ideas!

Mohammad Azam
www.azamsharp.net

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-04 : 19:52:49

>> I know I can use @@ERROR after every INSERT but I think there should be some easier way.
Not in v2000

In v2005 you can use a try-catch block.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-12-05 : 12:00:51

Wow Sweet! I am currently using v2000 so I guess I am stuck with @@Error.

Mohammad Azam
www.azamsharp.net

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 13:50:00

I think you will be lucky to catch an @@ERROR in this circumstance. I reckon your Sproc is going to just "bail" on the constraint.

If you are not already using it have a look at

SET XACT_ABORT ON

which will cause all errors to abort your SProc. This may be a bit of a blunt-instrument, but it does stop errors going un-catched.

Kristen

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-12-05 : 15:08:24

Thanks Kristen!.

So, what is the best practice when inserting into multiple tables which have no relationship between them. I know this is kinda ackward but just asking. I can use the XACT_ABORT (suggested by you :)) or I can check for @@ERROR after each insert.

Thanks, Azamsharp

Mohammad Azam
www.azamsharp.net

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 16:57:02

[code]
BEGIN TRANSACTION ...
INSERT INTO TABLE1 ...
IF @@ERROR <> 0 GOTO MyAbort
INSERT INTO TABLE2 ...
IF @@ERROR <> 0 GOTO MyAbort

COMMIT
GOTO MyAllDone

MyAbort:
ROLLBACK

MyAllDone:
[/code]
is fine. I just don't think that this will catch a Constraint Error. But it needs testing because I'm not sure.

You can also test how XACT_ABORT will handle such situations, and whether the way that it handles it is appropriate for the application you are working on.

Kristen

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-12-05 : 17:16:17

What commit mode are you?

The most common mistake is not understanding the impact this has...

SET IMPLICIT_TRANSACTIONS ON.. Look it up in BOL

DavidM

Production is just another testing cycle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources