February 15, 2004 at 5:05 pm
Would like to find out how to debug a trigger. Hopefully there is a debugging tool available.
TIA Gary Andrews garywandrews@cox.net
February 15, 2004 at 5:41 pm
Query Analyzer has a debugger, just fire some code that would activate the trigger, then step through it.
February 16, 2004 at 4:02 am
Trigger can not be debugged through SQL Query Analyzer..
What you can do is to stimulate the same enviornment that would be there in a trigger. i.e. create 2 tables by name "inserted" and "deleted" , populate them with as if they were in a trigger. and convert your trigger code into a SP.
you can debug that SP easily.
-- Amit
February 16, 2004 at 5:35 am
I have found it useful to use print statements in the trigger. Then perform an action in query analyzer that will cause the trigger to be fired. The print statements within the trigger will be printed out in the output messages in query analyzer. This is a great way to see determine if conditional statements are working and what the values of variable are at certain points.
Pat
Pat Buskey
February 16, 2004 at 7:05 am
I haven't used either but ... I know that Embarcadero has a SQL debugger, and I think that Visual Studio also has one. They both claim to work on triggers.
Hopefully your trigger is short and sweet and you can just cut and paste the offending code into a query window.
You can use aliases to the base table to fake the virtual inserted and deleted tables.
insert into AuditTrail
select .... stuff goes here ....
from MyTable as inserted
join MyTable as deleted on inserted.keyid = deleted.keyid
where .... more stuff goes here ....
February 16, 2004 at 7:16 am
I also find SQL Profiler useful in debugging triggers.
February 16, 2004 at 7:32 am
Profiler is a good tool, but what I use more often--especially with complex triggers is a custom log table, sort of like your own custom profiler. Typically, I'll create the log table with at least a datetime column and a varchar column to trap the time of the action and the action performed by the trigger; other useful columns are those that trap the values of variables used in the trigger. Essentially, at each step in the trigger, I insert a record into the custom table, using getdate() to populate the datetime column. Then I can see exactly what happened, when it happened, and how long it took (handy for troubleshooting speed problems). This technique is also handy for debugging stored procedures and user-defined functions.
February 16, 2004 at 3:54 pm
The easiest way I have found to debug a trigger is to create a simple stored procedure that will fire the trigger. Then debug the stored procedure in Query Analyzer or Visual Studio and it will step into the trigger.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply