c# - SQL know what statement activated a trigger if the trigger fails -

i'm using sql server in c# application, , i'm using batch statements. problem i'm having in case statement fails, whatever reason, need know statement failed , return it.

i've managed putting try catch around sql statement (so won't quit if fails) c# cmd.executequery returns number of rows affected.

the problem i'm having if failure occurs because of trigger, trigger outside of try catch of original batch , won't caught, instead caught c# application. @ point, state of batch lost in limbo , whole thing fails.

i need way of knowing statement (or number of rows affected far, or index in batch) triggered trigger caused exception. whether there's can in sql or c#, i'm open whatever.

thanks.

this isn't answer it's far big comment , need formatting.

say have statement begin try insert jazzhands values (2) end try begin catch end catch. if table jazzhands has trigger inserts whatever in other table, if trigger fails, won't cycle , caught in catch original insert. transaction fails altogether

i have no idea why think that. following sql:

create table jazzhands (id int not null,constraint ck_id_jh check (id < 10)) go create table jazzhands2 (id int not null,constraint ck_id_jh2 check (id < 5)) go create trigger t_jazzhands on jazzhands after insert     insert jazzhands2(id)     select id inserted go --1 begin try     insert jazzhands (id) values (11)     print 'no error 1' end try begin catch     print 'error 1' end catch go --2 begin try     insert jazzhands (id) values (6)     print 'no error 2' end try begin catch     print 'error 2' end catch go --3 begin try     insert jazzhands (id) values (1)     print 'no error 3' end try begin catch     print 'error 3' end catch go 

produces:

(0 row(s) affected) error 1  (0 row(s) affected)  (0 row(s) affected) error 2  (1 row(s) affected)  (1 row(s) affected) no error 3 

and both tables contain single row. that, is, contrary assertion, when either within table prevents insert succeeding, or within trigger on table prevents insert succeeding, same error handling occurs, , it's indistinguishable.

---

if have actual example failure of trigger produces different behaviour failure directly on table, please edit such example question.