trigger commits

[jfkuser]

Hi,

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.

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.

Whats going wrong here?

Thanks in advance.

jfkuser

[monalisag]

You can use save transcations after each insert statemnest and also trap the error using @@error after each insert statement. In this way you can know which insert failed and cal rollback till that point and commit the rest.

[jfkuser]

This also commits changes made in the original table TBL_TEST. I want to rollback any inserts / updates on TBL_TEST if any of the other 4 table inserts fail.

But 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.

---jfk