I have a Insert,Update trigger on a table called TBL_TEST . I am doing inserts in 4 tables inside the trigger. But if any one of these insert fails I want to keep the changes made in any previous table but rollback the insert / update made on TBL_TEST.
Lets say if it fails on insert of 3rd table, I want to commit inserts made on last 2 tables and rollback insert/update on TBL_TEST. If I use save tran it commits changes made in TBL_TEST too.
When I encounter an error and issues rollback its rolling back all the inserts. Even though I am using exclusive begin tran - Commit Tran for every insert statement.