Posted by Hugo Kornelis on 01/25/06 22:22

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

[Back to original message]