Re: Trigger Error

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

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

Date: Wed, 23 Jun 2004 13:57:03 +0100

I am new to SQL - do you have any sample of how I might do this that I can
wrok from?

Thanks

"Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message
news:uV02FkRWEHA.4056@TK2MSFTNGP11.phx.gbl...
> The only way I can think of,,, is to write an INSTEAD of trigger...
>
> The instead of trigger can see the new value of the text field , and you
can
> look up the existing value. Then audit both.
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Keith" <@.> wrote in message
news:uGrAPjQWEHA.3236@tk2msftngp13.phx.gbl...
> > 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: application roles
    ... I 've made a trigger on a SQL2000 database to test your solution with the ... But my login-account and database user appear in my logtable instead of the ... if you are on SQL 2005. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • 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: Transaction Abort in a trigger SQL 2008, writing to event log
    ... creating another transaction within the trigger. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • 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)