CODEWITHSUNDEEP
asp.netsql-servervisual-studiodebuggingssms
g.pickardou
g.pickardou

Reputation: 35873

How to debug a trigger in SQL Server what is executed by an ASP Web app?

Context:

I have a (legacy) ASP.NET app using a class lib which's data access is calling SPs using ad dal.dbml and its generated code.

I am using VS 2015 Enterprise update 1, have locally installed Microsoft SQL Server 2014 - 12.0.4213.0 (X64) (Build 10586) and SSMS 2014.

Question

When running the Web application the app inserts rows to a table which I recently implemented an insert trigger. How can I debug the actual trigger execution "triggered" by the Web application? Note: I do know how to interactively debug SPs in SSMS. Now that is not I want, because It is nearly impossible to reproduce and parametrize what the web app does. So I would like to debug via the Web App.

Is this possible at all? (Seems to be a pretty usual need, though I unserstand it requires very complex debugging infrastructure and services)

Upvotes: 0

Views: 2286

Answers (1)

HLGEM
HLGEM

Reputation: 96572

What you do is you run Profiler to get the typical values being sent when the error occurs.

Then the easiest way I know of to troubleshoot something like this is to take the trigger code out of the trigger and put it in a script using #inserted (and/or #deleted if need be) instead of the pseudotables accessed by the trigger.

The insert the data the stored proc would insert into the table into the #inserted table. Then you can take the trigger step by step and even look at the results of a select that is used in an insert.

The most common error I have found in triggers is that they are set up to handle only one record in inserted rather than multiple records. SO if you find that what your proc would put in for the values you get is one record, that may be the issue. Or you may be missing a required field. IN that case you may need adjust the proc to put in the value or assign a default.

Upvotes: 2

Related Questions