SQL Triggers for Auditing

Discussion in 'ASP General' started by Keith, Jun 24, 2004.

  1. Keith

    Keith Guest

    Not sure if anyone in here knows the answer to this, but I asked in a SQL
    group and haven't had a suitable answer and since the front end app is ASP I
    though I'd give here a try.

    I am trying to create a simple trigger in my SQL DB so that when a record is
    updated or deleted a copy of the original record is placed in an audit
    table.

    However, I keep getting the following error:

    Server: Msg 311, Level 16, State 1, Procedure SYS_Individual_AUDIT_Trigger,
    Line 9
    Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
    tables.

    Now apparently this is because the TEXT column I have cannot be 'logged' or
    so someone told me.

    Is there any way of using a trigger (an idiot proof way) so that I can copy
    a record to an audit table when it is updated/deleted if the record contains
    one or more TEXT columns?

    If all else fails I will write it into my app but I am trying to avoid that
    if possible.

    Thanks
     
    Keith, Jun 24, 2004
    #1
    1. Advertising

  2. Keith wrote:
    > Not sure if anyone in here knows the answer to this, but I asked in a
    > SQL group and haven't had a suitable answer and since the front end
    > app is ASP I though I'd give here a try.
    >
    > I am trying to create a simple trigger in my SQL DB so that when a
    > record is updated or deleted a copy of the original record is placed
    > in an audit table.
    >
    > However, I keep getting the following error:
    >
    > Server: Msg 311, Level 16, State 1, Procedure
    > SYS_Individual_AUDIT_Trigger, Line 9
    > Cannot use text, ntext, or image columns in the 'inserted' and
    > 'deleted' tables.
    >
    > Now apparently this is because the TEXT column I have cannot be
    > 'logged' or so someone told me.
    >
    > Is there any way of using a trigger (an idiot proof way) so that I
    > can copy a record to an audit table when it is updated/deleted if the
    > record contains one or more TEXT columns?
    >
    > If all else fails I will write it into my app but I am trying to
    > avoid that if possible.
    >
    > Thanks


    If you are using SQL 2000, you can use an "Instead Of" trigger. Normal
    trigger fire after the triggering action occurs, so the only place to find
    the old data is in the deleted table. Instead Of triggers fire before the
    triggering action is performed, allowing you to perform some activity
    instead of the activity that would have been performed by the triggering
    action. look it up in SQL BOL (SQL Books Online) and post any follow-up
    questions you have to m.p.sqlserver.programming.

    If pre-SQL7, then you have no recourse but to perform all updates and
    deletions via stored procedures which copy the original data to the audit
    table before performing the intended action.

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 24, 2004
    #2
    1. Advertising

  3. Would copying the first 8000 characters be enough? Do you really want to
    store a copy of the entire row?

    I suppose if you had a replica table, and the object table had a primary
    key, you could do something like this in an instead of trigger:

    INSERT tableCopy SELECT col1, col2, col3 FROM objectTable WHERE pk IN
    (SELECT pk FROM inserted)

    But man, that performance would not be good. And, you would have to handle
    the normal operation as well (since the instead of trigger stops it from
    happening).

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Keith" <@.> wrote in message news:...
    > Not sure if anyone in here knows the answer to this, but I asked in a SQL
    > group and haven't had a suitable answer and since the front end app is ASP

    I
    > though I'd give here a try.
    >
    > I am trying to create a simple trigger in my SQL DB so that when a record

    is
    > updated or deleted a copy of the original record is placed in an audit
    > table.
    >
    > However, I keep getting the following error:
    >
    > Server: Msg 311, Level 16, State 1, Procedure

    SYS_Individual_AUDIT_Trigger,
    > Line 9
    > Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
    > tables.
    >
    > Now apparently this is because the TEXT column I have cannot be 'logged'

    or
    > so someone told me.
    >
    > Is there any way of using a trigger (an idiot proof way) so that I can

    copy
    > a record to an audit table when it is updated/deleted if the record

    contains
    > one or more TEXT columns?
    >
    > If all else fails I will write it into my app but I am trying to avoid

    that
    > if possible.
    >
    > Thanks
    >
    >
     
    Aaron [SQL Server MVP], Jun 24, 2004
    #3
  4. Keith

    Keith Guest

    Need to capture teh entire record as the data in it will be legally binding
    and the slightest change could be crucial.

    Performance is not too much of an issue as it will not be heavy on useage,
    but the useage there is needs auditing.

    "Aaron [SQL Server MVP]" <> wrote in message
    news:...
    > Would copying the first 8000 characters be enough? Do you really want to
    > store a copy of the entire row?
    >
    > I suppose if you had a replica table, and the object table had a primary
    > key, you could do something like this in an instead of trigger:
    >
    > INSERT tableCopy SELECT col1, col2, col3 FROM objectTable WHERE pk IN
    > (SELECT pk FROM inserted)
    >
    > But man, that performance would not be good. And, you would have to

    handle
    > the normal operation as well (since the instead of trigger stops it from
    > happening).
    >
    > --
    > http://www.aspfaq.com/
    > (Reverse address to reply.)
    >
    >
    >
    >
    > "Keith" <@.> wrote in message

    news:...
    > > Not sure if anyone in here knows the answer to this, but I asked in a

    SQL
    > > group and haven't had a suitable answer and since the front end app is

    ASP
    > I
    > > though I'd give here a try.
    > >
    > > I am trying to create a simple trigger in my SQL DB so that when a

    record
    > is
    > > updated or deleted a copy of the original record is placed in an audit
    > > table.
    > >
    > > However, I keep getting the following error:
    > >
    > > Server: Msg 311, Level 16, State 1, Procedure

    > SYS_Individual_AUDIT_Trigger,
    > > Line 9
    > > Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
    > > tables.
    > >
    > > Now apparently this is because the TEXT column I have cannot be 'logged'

    > or
    > > so someone told me.
    > >
    > > Is there any way of using a trigger (an idiot proof way) so that I can

    > copy
    > > a record to an audit table when it is updated/deleted if the record

    > contains
    > > one or more TEXT columns?
    > >
    > > If all else fails I will write it into my app but I am trying to avoid

    > that
    > > if possible.
    > >
    > > Thanks
    > >
    > >

    >
    >
     
    Keith, Jun 24, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robey Holderith

    Python Code Auditing Tool

    Robey Holderith, Feb 2, 2005, in forum: Python
    Replies:
    9
    Views:
    457
    Skip Montanaro
    Feb 2, 2005
  2. JimLad
    Replies:
    0
    Views:
    345
    JimLad
    Sep 12, 2006
  3. CptDondo

    Auditing C code

    CptDondo, Sep 19, 2006, in forum: C Programming
    Replies:
    9
    Views:
    487
    Chris Torek
    Sep 24, 2006
  4. Elhanan

    auditing with context?

    Elhanan, Mar 12, 2009, in forum: Java
    Replies:
    4
    Views:
    374
    Arved Sandstrom
    Mar 13, 2009
  5. RM

    Auditing .net generated files

    RM, Oct 6, 2009, in forum: ASP .Net
    Replies:
    0
    Views:
    302
Loading...

Share This Page