Re: Audit trails for changed records

From: CD (mcdye1@hotmail.com)
Date: 08/08/02 

From: "CD" <mcdye1@hotmail.com>
Date: Thu, 8 Aug 2002 15:35:09 -0500

How would you code it to ignore it if C3 changed, but you still wanted the
record if C1 or C2 was touched?

"lindawie" <lindawie@my-deja.com> wrote in message
news:uxwz2LvPCHA.2688@tkmsftngp11...
> Sue,
>
> > Hi I am using SQL 7.0 and MS Access. I would like to be
> > able to log to a table information whenever a record is
> > changed.
> >
> > What's the best way of doing that?
> >
> > I can track the transactions with the profiler by tracing
> > all RPC:completed events with the txt begining with
> > sp_executesql.
> >
> > I would like to have the info in a table I can view in
> > access.
>
> The easiest way it to do this through a trigger.
> Create a history table that has the same structure as the
> table you want to audit, plus an additional column for the
> primary and perhaps a timestamp column.
>
> In your trigger, insert from the deleted rtable into the
> history table. The main table contacts the current data and
> the history table conatins all past data. A union of main table
> and history table gives you a complete picture of all activity.
>
>
> create table t1(c1 int, c2 int)
> create table t1_hist (c0 int identity, c1 int, c2 int,
> moddate datetime default getdate())
> go
> create trigger tr_trg on t1 for update, delete
> as
> insert t1_hist (c1, c2) select c1, c2 from deleted
> go
> insert t1 select 1, 1 union select 2, 2 union select 3, 3
> select * from t1
> update t1 set c2 = c2 * 10 where c1 > 1
> delete t1 where c1 = 2
> select * from t1
> select * from (
> select c0, c1, c2, moddate from t1_hist
> union all
> select null, c1, c2, null from t1) hist
> order by c1, moddate
> go
> drop table t1
> drop table t1_hist
>
>
> Linda
>
>

Relevant Pages

  • Re: Audit trails for changed records
    ... The easiest way it to do this through a trigger. ... Create a history table that has the same structure as the ... A union of main table ... create table t1(c1 int, c2 int) ...
    (microsoft.public.sqlserver.security)
  • 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: Historical Database.
    ... if you are using sql server 2005 sp2 then you can use ddl auditing. ... a trigger can be created in order to perform any action upon a DDL ... ones u have created table now create a trigger to capture events in database ... now the problem is if I create User History table and in that I have ...
    (microsoft.public.sqlserver.clients)
  • Re: Update trigger
    ... I need to write a history record for certain fields that change on a ... nvltrigger for each column for example. ... the actual journaling process.. ... If something has changed, write a history row, with all the old and new values. ...
    (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( ...
    (comp.databases.oracle.misc)