How to detect that trigger fails after calling a stored procedure from .Net application?

Question

We have a .NET application (VB / VS2010) and are calling many stored procedures on a SQL Server 2008 for database queries. We also have quite a few update/insert/delete triggers that are executed automatically once these stored procedures modify the database tables.

There are quite often situations when a stored procedure is called and it seems to perform ok as no error is raised and the .NET app continues as usual. However if I then look under the covers and perform the stored procedure call manually via SQL Server Client I see that a trigger that's executed right after the stored procedure fails, thus rolling back all changes.

So my question is: what's the best way to detect and pass through errors in our .NET --> stored procedure --> trigger scenario to know for sure in the .NET app that everything succeeded or not in case of an error?

Many thanks in advance, Steve

---

Update: I am at home now and away from my desk (and the code base) for the weekend, so won't have a chance to check the very details of the stored procedures. Thanks so much for the answers given so far. I can have a look at the code again next week.

But in the meantime...

One question was about the version of MS SQL Server: it's 2008.

From what I know out of the top of my head we are calling the stored procedures (at least those that don't read data and "just" update, delete or insert data) in this manner:

Using connection As New SqlConnection("connectionString")
    Dim command As New SqlCommand("EXEC STORED_PROCEDUR_ENAME), connection)
    command.Connection.Open()
    command.ExecuteNonQuery()
End Using

I think the assumption behind the code above is the expectation that if something fails within the stored procedure or a related trigger, that

command.ExecuteNonQuery()

would then fail. That might be my first problem, i.e. will I have to alter this code? One question below was if I use ExecuteDataReader, so the answer is no, at least not so far...

I'll comment on the SQL specific questions and suggestions below.

Answer 1

I actually had the same issue today,

to solve it i used an output @msg and placed it after each function in the sproc.

SET @msg = 'Test print 1'

each section i added one so i know what ever the last number printed was where the sproc failed. I then went to the table where the trigger was failing and adjusted it until it printed the last number and passed.