Re: SQL Triggers for Auditing

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 16:37:42 -0400

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"

Relevant Pages

  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... record update even if we are only effectively changing one column ... SQL> create or replace trigger emp_upd_trig ... SQL> update emp ...
    (comp.databases.oracle.server)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ... So I figured a trigger on the ... SQL> create table inactive( ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)
  • Re: on delete set null
    ... > Nun versuche ich das ganze mit einem Trigger zu simulieren (auf SProcs ... SQL Server verlangt ... > CREATE TABLE voranstellen kann, in denen die Tabellen erzeugt werden, ...
    (microsoft.public.de.sqlserver)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ...  So I figured a trigger on the ... SQL> create table inactive( ...
    (comp.databases.oracle.misc)