Re: SQL Triggers for Auditing

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 16:53:28 -0400

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:O3LDJaiWEHA.2288@TK2MSFTNGP10.phx.gbl...
> 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
>
>

Relevant Pages

  • Re: SQL Triggers for Auditing
    ... > SQL group and haven't had a suitable answer and since the front end ... > in an audit table. ... > Is there any way of using a trigger so that I ... trigger fire after the triggering action occurs, so the only place to find ...
    (microsoft.public.inetserver.asp.general)
  • Re: Whats wrong with this trigger
    ... the app that uses this data is .NET. ... > same values for the primary key as the ones used in the original tables. ... >>>update trigger that inserts an audit row into a view. ... >>>identity and so is the view that I'm inserting the audit row into. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: sql CLR trigger causing havoc in Access linked table
    ... Your problem has nothing to do with the fact that you are using CLR or not ... T-SQL for your insert trigger. ... setting an heavy load on the sql server. ... One reason to move to SQL05 is the use of CLR for a generic audit trigger ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Determine when App Receives Focus
    ... What would trigger this code? ... But assuming the IsActive tells you if the app has ... So, I implemented a timer that checks every 1 second, using debug.print ... without the loop, 0%. ...
    (microsoft.public.vb.general.discussion)
  • Re: Can a SQL trigger raise an event in C#?
    ... C# app should trigger that, not the database. ... SQL Server really doesn't have any knowledge ...
    (microsoft.public.dotnet.framework.windowsforms)