Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: Trigger commit / Rollback

    trigger commits
    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.

    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.

    Whats going wrong here?

    Thanks in advance.

    jfkuser

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    begin tran
    save transaction test0
    create table #test(x int)
    save transaction test1
    insert #test(x) values (1)
    save transaction test2
    insert #test(x) values (2)
    select * from #test
    rollback tran test2
    select * from #test
    rollback tran test1
    select * from #test
    /*
    rollback tran test0
    select * from #test
    */
    GO
    select @@trancount
    rollback tran

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •