Re: Trigger Error

From: Keith (_at_.)
Date: 06/23/04 

Date: Wed, 23 Jun 2004 10:59:44 +0100

Is there any way of capturing the previous value of a TEXT column with a
trigger to audit the tables fully?

Thanks

"Hari" <hari_prasad_k@hotmail.com> wrote in message
news:usaalfQWEHA.1888@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> You cant do this with the datatype ntext , text and image, because thse
> datatypes are not logged.
> so trigger will not get the the previous state of deleted or updated
field.
>
> --
> Thanks
> Hari
> MCDBA
>
> "Keith" <@.> wrote in message
news:ui4phRQWEHA.3024@TK2MSFTNGP09.phx.gbl...
> > I am very new to SQL and have been building my triggers based on
examples
> > people have given me etc.
> >
> > My trigger is to copy the entire record into an audit table on
> > update/delete. Some of my columns are TEXT.
> >
> > I am getting an error saying:
> >
> > 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.
> >
> >
>
>

Relevant Pages

  • Re: trigger counter
    ... DATETIME, store GETDATEin it, and use that "identifier"? ... > I am trying to put an audit system on a table to be used as a change order ... > The trigger runs on an update and checks each field to see whether or not ... > an audit table. ...
    (microsoft.public.sqlserver.programming)
  • Re: Possible as ANSI SQL?
    ... In order for the application to write to the audit ... trail, it has to disable that protection trigger. ... >> interacts with SQL server. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to access field contents generically in trigger
    ... I'm not sure about the scope problem you mentioned, but as I'm still not seeing the field contents, I guess that must be what's wrong now. ... You can't have one trigger for all tables. ... I copy all old and new data in the audit ... you'll find that @FieldContents is not ...
    (microsoft.public.sqlserver.programming)
  • Re: Strange Trigger Behavior
    ... Since you need write to the log only of there is a change in the standard ... Then, the trigger will not insert a record, if there is ... > to alert us if a change was made to a standard cost. ... > A coworker of mine pointed out that about 200,000 rows hit the audit file. ...
    (microsoft.public.sqlserver.programming)
  • Re: audit trigger
    ... There is no DDL for the audit table and even if it did work ... The method you use to create the value of @PKCols is not ... > I am trying to implement an audit trigger I found on the net. ... > table wo_main does have a primary key 'entry'. ...
    (microsoft.public.sqlserver.programming)