Professional Web Applications Themes

advanced: Transaction-logging Trigger - Microsoft SQL / MS SQL Server

I had already tried using temporary tables, but the problem with them is that my base table might include columns of types text, ntext, or image. If you try the command SELECT * INTO #inserted FROM inserted that you suggested on a base table containing the above data types, sql server returns the error: Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables. so you somehow need to filter-out columns of specific data-types when inserting to the temporary table, which brings us back to where we started. A dynamic sql insert string has to be created ...

  1. #1

    Default Re: advanced: Transaction-logging Trigger


    I had already tried using temporary tables, but the problem with them is
    that
    my base table might include columns of types text, ntext, or image.
    If you try the command
    SELECT * INTO #inserted FROM inserted
    that you suggested on a base table containing the above data types, sql
    server returns the error:
    Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
    tables.

    so you somehow need to filter-out columns of specific data-types when
    inserting to the
    temporary table, which brings us back to where we started. A dynamic sql
    insert string has to be created
    and sent to a system stored procedure for execution; in which case, all
    references to
    the 'inserted' and 'deleted' tables fall out of scope again.

    Is there a way in transact-sql to refer to columns by their index and not
    their name?
    Like you can do in ADO for example, where all the following expressions are
    equivalent:
    recordset!FieldName
    recordset("FieldName")
    recordset(FieldIndex)
    which is really convenient

    In that case the following (portable) code could be used in the trigger:

    while col < ...
    ....
    set col = col + 1

    insert into [My_Log_Table] (... , Old_Value , New_Value , ...)
    select ... , deleted.??? , inserted.??? , ...
    from deleted join inserted on ...
    ....
    end

    where ??? is direct a reference to the column by its ordinal position or any
    other index, not it's name
    --hence, no system stored procedure is required to execute it.

    Vasilis


    vsiat Guest

  2. #2

    Default Re: advanced: Transaction-logging Trigger

    vsiat (npalpanafonet.gr) writes:
    > Is there a way in transact-sql to refer to columns by their index and not
    > their name?
    Thankfully, no!

    I suggest that you review this paragraph of my previous posting:

    However, you may not be the first to implement this. There are some
    third-party products out there. At [url]http://www.redmatrix.com/[/url] there is
    something which is trigger-based. Interesting is also Entegra from
    [url]http://www.lumigent.com[/url] which gets the data directly from the transaction
    log, for quite some lower performance cost than your trigger.

    This can save those who are paying for your work a lot of money.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

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

Similar Threads

  1. disable trigger from transaction
    By Postgres General in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 25th, 11:19 AM
  2. WS-Transaction
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 13th, 12:25 AM
  3. Replies: 5
    Last Post: September 18th, 09:15 AM
  4. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM
  5. Advanced logging;
    By Miha Pihler in forum Windows Setup, Administration & Security
    Replies: 1
    Last Post: July 15th, 05:43 AM

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