I have one table (so called A) in my MS SQL Server database and I created after insert trigger on it that processes the inserted data and inserts some of the columns into another table (let's say B).
So far it works pretty well, but I have a problem when the second insert (triggered) into table B is not proper (ex. inserted values are not in line with integrity constraints). In this case all transactions are rolled back, especially the first insert into table A.
I'd like to have values inserted into table A even if the second insert (into table B) was wrong and was not accomplished. I've tried several versions with TRY/CATCH block but at anytime my trigger throws an error and all transaction is rolled back. Is there any way to work around this issue? Thanks in advance.
