Re: Audit trails for changed records

From: lindawie (lindawie@my-deja.com)
Date: 08/08/02 

From: "lindawie" <lindawie@my-deja.com>
Date: Thu, 8 Aug 2002 08:50:49 -0700

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: User Functions in batch inserts
    ... Sorry for the late reply...Here is a quick example of instead of trigger... ... create table t(i int primary key, ... union all select 999,newid ... >>> newid()) ...
    (microsoft.public.sqlserver.programming)
  • Re: User Functions in batch inserts
    ... instead of trigger... ... >create table t(i int primary key, ... >union all select 999,newid ...
    (microsoft.public.sqlserver.programming)
  • Re: Iterate through the inserted table in an update trigger...
    ... CREATE TRIGGER trffc_AmendmentTracking ON dbo.ffc_AmendmentTracking ... @HistoryMasterID int, ... case ColName ... Union All Select N'Amendment' ...
    (microsoft.public.sqlserver.programming)
  • 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, ...
    (microsoft.public.sqlserver.security)
  • Re: Whats happening to our country?
    ... It comes from households and business with excess cash and goes to ... If you are aware of labor history and choose big business over ... you a member of the musicians union or are you a scab? ... corporations own just about all there is to own in this country. ...
    (rec.music.classical.guitar)