sql server transactions not committing within an instead of insert trigger

I have some code in an instead of trigger(on a table) that performs the following
begin try
declare@ .........
 set @erred = 0  
 begin transaction @t1
set @erred = 0
insert into table x from inserted ......

commit transaction@t1
set @erred =0
end try
begin catch
        if @erred = 1 begin	rollback transaction @t1 END

end catch

This transaction is placed on tables 1,2,3,4 and 5
I execute the transaction on table 1  by inserting valid data into table 1.  The trigger successfully sends data into table x.

I execute the transaction on table 2 by inserting invalid data into table 2, thus forcing an error. the trigger doesn't add any new data, but the data inserted by the successful transaction in table 1also gets deleted.

Why is that? I.e why does the 'old' data get deleted as well.Does the transaction stay open for some reason all the time. The text for @t1 is different in each trigger. I can guarantee that there are no delete statements in the code
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It is the default behavior of a transaction that it will rollbacks everything when the failur occurs until and the you have to commit that.

Use different transactions for these operations and don't rollback any transactions i.e. If t2 fails rollbackt1.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adimitAuthor Commented:
What I don't get is that it was another trigger rolling back the transaction of some other trigger. The triggers are different, the transactions are different
Doug BishopDatabase DeveloperCommented:
You have a BEGIN TRANSACTION. You insert into TABLE x, this begins the transaction in the trigger for TABLE x. You have not yet committed the initial transaction, because the trigger code in TABLE x is now executing. If you do a ROLLBACK TRANS in the trigger code for TABLE x, that rolls back everything, including your INSERT into TABLE 1. The subsequent COMMIT TRANS in the original table has nothing to commit, becuase the data has been rolled back. If you want to commit the data in table 1, regardless of whether or not the data is successfully inserted into the subsequent tables, then place that code outside of the BEGIN TRANS.

A BEGIN TRANS makes everything inside it all-or-nothing, even if you have COMMITs inside it. One ROLLBACK will completely undo everything starting with the BEGIN TRANS. There is no way to selectively COMMIT some data and ROLLBACK other data.
adimitAuthor Commented:
see final comment
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.