MS SQL Atomic Inserts
Posted on 2004-11-16
I'm running an ASP script where I'm trying to insert data into 6 tables.
Now, if even ONE of these insert operations fails, then I want to rollback all of the successful inserts up to that point
So far, the SQL code I have looks like this:
BEGIN TRANSACTION xact
INSERT INTO tableA values('a','1')
INSERT INTO tableB values('a','2')
INSERT INTO tableC values('a','3')
INSERT INTO tableD values('a','4')
INSERT INTO tableE values('a','5')
INSERT INTO tableF values('a','6')
COMMIT TRANSACTION xact
I don't know how to get SQL to do something like:
if there were any errors, then ROLLBACK TRANSACTIOn xact, else COMMIT TRANSACTION xact.
Is there any way to do this without using stored procedures or triggers?
Thanks very much for any assistance.