Professional Web Applications Themes

Debugger with triggers - Microsoft SQL / MS SQL Server

I am trying to debug some trigger code and want to be able to examine the contents of inserted, deleted, temporary tables etc. There doesn't seem to be a way to do this directly from the debugger so I hit on the idea of using a local variable to control the selecting of these tables. Code follows... if exists (select 1 from sysobjects where name = 'test' and type = 'P') drop procedure test; if not exists (select 1 from sysobjects where name = 't1' and type = 'U') CREATE TABLE t1 (a int PRIMARY KEY) if exists (select 1 ...

  1. #1

    Default Debugger with triggers

    I am trying to debug some trigger code and want to be able to examine
    the contents of inserted, deleted, temporary tables etc. There doesn't
    seem to be a way to do this directly from the debugger so I hit on the
    idea of using a local variable to control the selecting of these tables.
    Code follows...

    if exists (select 1 from sysobjects where name = 'test' and type = 'P')
    drop procedure test;
    if not exists (select 1 from sysobjects where name = 't1' and type = 'U')
    CREATE TABLE t1 (a int PRIMARY KEY)
    if exists (select 1 from sysobjects where name = 'tr1' and type = 'TR')
    drop trigger tr1
    go
    create trigger tr1 on t1 instead of insert
    as
    begin
    declare debug tinyint
    select debug = 0;
    if debug = 1
    begin
    select * from inserted
    end
    end
    GO
    create procedure test as
    begin tran
    INSERT INTO t1 VALUES (1)
    commit tran
    go

    If I run the sp using the debugger and step into the trigger I can
    change the value of debug from 0 to 1 and force the selection of the
    data I want to see. If the code is run normally then no selection occurs
    and the network/workstation doesn't get swamped with mountains of data.
    Is there a better way of doing this? I can't see how to examine the
    contents of a local table any other way - I was kind of expecting to
    have an 'immediate' window in which I could do these selections.

    Matt

    Matt Guest

  2. #2

    Default Re: Debugger with triggers

    Matt (co.uk) writes: 

    Better and better... An alternative is to do something like:

    if object_id('tempdb..#debug$') IS NOT NULL
    -- debug code goes here.

    This permits you run an SQL statement from QA and get the debug output
    without involving the debugger.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: Debugger with triggers

    Erland Sommarskog wrote:
     
    >
    > Better and better... An alternative is to do something like:
    >
    > if object_id('tempdb..#debug$') IS NOT NULL
    > -- debug code goes here.
    >
    > This permits you run an SQL statement from QA and get the debug output
    > without involving the debugger.[/ref]


    That's fine if

    a) your debug cycle allows you to do a full run and look at the whole
    results (and you've got disk space to capture the results for what may
    be a large set of returned datasets)
    b) you don't mind tempdb being read each time the 'if' statement
    executes in your production system - that's a measurable overhead.

    Not quite sure what's better about your method though. It's different
    and it's an alternative and it allows you to 'debug' without the
    debugger but why is it better? I'm not too impressed with the debugger
    so far but do you prefer to search through masses of debug output rather
    than using the debugger? (I have managed for ten years without the
    debugger by building up code in query yser until it works and then
    wrapping it as stored procedures only when I am fairly sure that the
    bits do as I want so I am not desparate to dive into the debugger if it
    isn't too useful).

    What I was really looking for is a facility to do adhoc, tightly
    specified queries (on temp tables, inserted etc) with the system halted
    in the middle of a trigger or a stored procedure.

    Matt

    Matt Guest

  4. #4

    Default Re: Debugger with triggers

    Matt (co.uk) writes: 

    "inserted" and "deleted" are created out of the log and are usually
    slow. Just a case from my own experience: I had a script that rebuilt
    a table by creating a new table and INSERT:ing over the old data.
    There were some 12000 rows in the table. The trigger contains a whole
    lots of checks. This INSERT took an hour. I replaced inserted/deleted
    with temptables. This slashed execution time to a few minutes.

    (As for having the trigger there at all - I want to know if there are
    any errors in my update script.)
     

    Hm, you never said which version of SQL Server you are using, did
    you? Bad blocking problems om tempdb..sysobject was a plaugue on
    6.5, and if you are indeed running 6.5, you should not follow my
    advice.

    Since we moved to SQL2000 we have not had very much problems with
    tempdb locks.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Trace triggers
    By Marek Lewczuk in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 13th, 03:53 PM
  2. triggers in DB2
    By Piotr in forum IBM DB2
    Replies: 2
    Last Post: August 27th, 03:37 PM
  3. Triggers
    By P. Saint-Jacques in forum IBM DB2
    Replies: 0
    Last Post: August 8th, 02:30 AM
  4. Help with triggers
    By W Gemini in forum IBM DB2
    Replies: 0
    Last Post: August 7th, 11:45 AM
  5. Diabled Triggers
    By BAbu in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 12th, 08:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139