CODEWITHSUNDEEP
triggerstransactionssql-server-2012
lkurylo
lkurylo

Reputation: 1651

rollback a nested transaction in trigger

I have a following situation. I have a table with trigger for insert. When I insert a row in it, from this trigger I want to insert some rows into a second table. For each of these rows I want to do it in it's own transaction in case something go wrong. I want to have original row in first table and all rows (these withous errors) in the second.

A little code to reproduce:

 create table test(id int primary key identity(1,1),name nvarchar(10))
    create table test2(id int primary key identity(1,1),
state char(1) check (state in ('a','b')))
    go

    create trigger test_trigger on test for insert
    as
    begin
    declare @char char(1)

    declare curs cursor for (
    select 'a'
    union
    select 'c'
    union
    select 'b')

    open curs

    fetch next from curs into @char

    while @@FETCH_STATUS = 0
    begin
        begin try
           begin transaction
              insert into test2(state)
              select @char
           commit
        end try
        begin catch
           print 'catch block'
           rollback 
        end catch
    fetch next from curs into @char
    end

    close curs
    deallocate curs

    end

    go

    insert into test(name) values('test')

    select * from test
    select * from test2
    go

    drop table test
    drop table test2

So for the sample data from this snippet, I want to have a row with 'test' in test table and two rows in the test2 table ('a' and 'b'). How can I write a code for that?

Upvotes: 1

Views: 314

Answers (1)

lkurylo
lkurylo

Reputation: 1651

Looks like finally I got it to work. Corrected trigger code:

create trigger test_trigger on test for insert
    as
    begin
    declare @char char(1)

    set xact_abort off

    declare curs cursor for (
    select 'a'
    union
    select 'c'
    union
    select 'b')

    open curs

    fetch next from curs into @char

    while @@FETCH_STATUS = 0
    begin
    save transaction t
        begin try

              insert into test2(state)
              select @char

        end try
        begin catch
           print 'catch block'
           rollback transaction t
        end catch
    fetch next from curs into @char
    end

    close curs
    deallocate curs

    end

Upvotes: 1

Related Questions