Can i debug/watch on the trigger's INSERTED and DELETED records/values?

[serge]

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.

Are there any documents, web links that describe ways
of debugging the trigger's INSERTED and DELETED?

Thank you

[Hugo Kornelis]

On Wed, 25 Jan 2006 13:29:59 -0500, serge wrote:

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?
Hi Serge,

No. During debugging, it is (unfortunately) not possible to see the
contents of ANY tables.

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?
You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.

Or you could use SELECT INTO or INSERT ... SELECT to store the values in
a persistant table.

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.
Hey, that's just what I suggested! <g>

This should work. Some potential reasons for why it didn't work for you
are:
- Maybe the code never even reached the insert into statement? This
might be the case if the table TABLE1 didn;t exist at all after trigger
execution.
- Did you check that the table TABLE1 did not exist before the trigger
was executed? If it did, the command above would result in an error (and
you should have gotten an error message).
- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
DELETE, or an INSERT .. SELECT that affected 0 rows)
- Don't use a temp table for this. It will be removed when the trigger
execution finishes, as it only exists in the scope of the trigger.
- In a DELETTE trigger, the inserted table is ALWAYS empty.

All the above are just guesses, of course. I'd have to see the actual
code to help you further.

Are there any documents, web links that describe ways
of debugging the trigger's INSERTED and DELETED?

Thank you

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.

In additions to Hugo's suggestions, keep in mind that if the trigger
fails, then the statement will be rolled back, and that includs the
data insertedvinto Table1
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Wed, 25 Jan 2006 22:27:44 +0000 (UTC), Erland Sommarskog wrote:

serge (se****@nospam.ehmail.com) writes:

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.

In additions to Hugo's suggestions, keep in mind that if the trigger
fails, then the statement will be rolled back, and that includs the
data insertedvinto Table1

Ah, of course. How could I forget it?

Time for bed, I guess :-)

Thanks, Erland!

--
Hugo Kornelis, SQL Server MVP

[serge]

Thanks Hugo, Erland.

Hugo,

This should work. Some potential reasons for why it didn't work for you
are:
- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
DELETE, or an INSERT .. SELECT that affected 0 rows)
I believe I was running an UPDATE statement with a zero-row operation.
But let's ignore that statement as now I've tested it again using a new
UPDATE statement that updates rows for sure. After testing the last 30
minutes I now understand things better.

I am able to INSERT the records from the trigger's INSERTED table
to the permanent table I created before running the update statement.

I also realized that when running in DEBUG mode I should make sure
to uncheck the DEBUG PROCEDURE's AUTO ROLL BACK check
box. This problem until I realized it kept me puzzled for 10 minutes.

You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.

Too bad ADD WATCH isn't available.
Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?

Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?

I haven't tried debugging in SQL 2005, as it you only can debug from
Visual Studio. But I would not really expect so.

Personally, I have more or less stopped using the debugger. It usually
works when you want to debug your local server, but when connecting to
another, there is so much red tape. Debug PRINTs and SELECTs are easier
to handle.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[serge]

> Personally, I have more or less stopped using the debugger. It usually

works when you want to debug your local server, but when connecting to
another, there is so much red tape.
Sometimes I do debug remotely (maybe often). Are you saying it is not always
a
safe approach to debug remotely? "There is so much red tape": these
are known issues that happen frequently or in the very rare cases?
Would you know if there are MS KB on these problems or personal
experiences led you to stop debugging remotely?

Can you please list some situations where debugging remotely is not safe
or the problems you ran into are complicated to explain?

If this is the case then I should avoid debugging remotely and end up
using Terminal Services to connect to the SQL Server and debug locally?

Debug PRINTs and SELECTs are easier to handle.

I just tried "SELECT * FROM INSERTED" and I saw the result
in the results pane so I am not sure why I had not tried this before.
Well, at least now I know and I won't need to create a table if I am
only interested in seeing the result during the debugging only.

Thanks Erland.

[Hugo Kornelis]

On Thu, 26 Jan 2006 01:19:30 -0500, serge wrote:

(snip)

You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.

Too bad ADD WATCH isn't available.
Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?

Hi Serge,

I don't know. I haven't seen the debugging capabilities in SQL 2005 yet.

In SQL 2000, the debugger was integral part of the product. But that has
been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.

http://lab.msdn.microsoft.com/Produc...6-f50123f6d235

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Sometimes I do debug remotely (maybe often). Are you saying it is not
always a safe approach to debug remotely? "There is so much red tape":
these are known issues that happen frequently or in the very rare cases?
Would you know if there are MS KB on these problems or personal
experiences led you to stop debugging remotely?

Unsafe? Yes, a little, although that was not really what I meant with
red tape. What I mean is simply that there are so many things have to
be aligned for it to work, that I don't find it worth the hassle.

Some time back, we found that debugging did not work when you had Windows
XP SP2 installed. I did some investigation, and found that hotfix
8.00.944 addressed this problem. (This hotfix is included in SP4.) I
installed hotfix on client and server. I also had to open port 135 in
Windows firewall. Now, port 135 is not any port: this is RPC, and a
prime attack surfaces for viruses. So opening port 135 is a little unsafe,
so there is all reason to only open it for the SQL Servers you want to
debug. (If is possible to open a port only for a certain IP address in
Windows firewall.) Eventually I got it working.

Then some months later, I felt like debugging again, but now I was out
of luck again. I did some inquires, and apparently our Windows admin had
decided to cut the number of permissions for the SQL Server service
account. I don't know exactly what permissions that are required, but
as it writes back to the client, it needs more than plain-user rights.

At this point, I just gave it up. These are not the only thing that
can stop debugging from working. And after all, what you can dig out
from the debugger can easily be achieved in other ways. Of course,
code that uses iterative approaches can be painful to debug that
way. But good SQL should not have much such code anyway. :-)

And, oh, there is one more possible issue with the debugger. Single-
stepping through a transaction is not that friendly if other users
needs to access the data.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[serge]

> Unsafe? Yes, a little, although that was not really what I meant with

red tape. What I mean is simply that there are so many things have to
be aligned for it to work, that I don't find it worth the hassle.

Some time back, we found that debugging did not work when you had Windows
XP SP2 installed. I did some investigation, and found that hotfix
8.00.944 addressed this problem. (This hotfix is included in SP4.) I
installed hotfix on client and server. I also had to open port 135 in
Windows firewall. Now, port 135 is not any port: this is RPC, and a
prime attack surfaces for viruses. So opening port 135 is a little unsafe,
so there is all reason to only open it for the SQL Servers you want to
debug. (If is possible to open a port only for a certain IP address in
Windows firewall.) Eventually I got it working.

Then some months later, I felt like debugging again, but now I was out
of luck again. I did some inquires, and apparently our Windows admin had
decided to cut the number of permissions for the SQL Server service
account. I don't know exactly what permissions that are required, but
as it writes back to the client, it needs more than plain-user rights.

At this point, I just gave it up. These are not the only thing that
can stop debugging from working. And after all, what you can dig out
from the debugger can easily be achieved in other ways. Of course,
code that uses iterative approaches can be painful to debug that
way. But good SQL should not have much such code anyway. :-)

And, oh, there is one more possible issue with the debugger. Single-
stepping through a transaction is not that friendly if other users
needs to access the data.

Thanks for the detailed explanation. Some interesting information
that I'll keep in mind.

[serge]

> In SQL 2000, the debugger was integral part of the product. But that has

been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.

http://lab.msdn.microsoft.com/Produc...6-f50123f6d235

Then I presume SQL 2005 Studio Management that comes with SQL 2005
is not a flavor of Visual Studio that can debug triggers.

Thanks Hugo.

[Hugo Kornelis]

On Thu, 26 Jan 2006 22:57:53 -0500, serge wrote:

In SQL 2000, the debugger was integral part of the product. But that has
been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.

http://lab.msdn.microsoft.com/Produc...6-f50123f6d235

Then I presume SQL 2005 Studio Management that comes with SQL 2005
is not a flavor of Visual Studio that can debug triggers.

Hi Serge,

That's correct. Management Studio is replacement for Enterprise Manager
plus Query Analyzer, but doesn't have the debugger.

--
Hugo Kornelis, SQL Server MVP