Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL Triggers for Auditing

Reply
Thread Tools

SQL Triggers for Auditing

 
 
Keith
Guest
Posts: n/a
 
      06-24-2004
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


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-24-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"


 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      06-24-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:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Keith
Guest
Posts: n/a
 
      06-24-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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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:(E-Mail Removed)...
> > 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
> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enforcing triggers in sql server database while using entity framework Andy B ASP .Net 1 01-26-2009 03:18 AM
Using SQL triggers on a database supporting an asp.net application ASP .Net 3 09-27-2008 07:10 PM
C2 auditing helensmith Software 2 05-27-2006 01:25 PM
MS SQL triggers and ruby Guillaume Marcais Ruby 5 04-24-2004 03:17 AM
Auditing question for the 70-270 MS book jones_net MCSE 2 10-22-2003 08:20 AM



Advertisments