SQL Triggers for Auditing

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

Bob
Thu Jun 24 15:37:42 CDT 2004

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"

Aaron
Thu Jun 24 15:53:28 CDT 2004

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
>
>

Keith
Thu Jun 24 16:04:36 CDT 2004

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]" <ten.xoc@dnartreb.noraa> wrote in message
news:ehjyz1iWEHA.1340@TK2MSFTNGP10.phx.gbl...
> 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
> >
> >
>
>