A couple of years ago, I investigated how to record any changes made to a
table. I have now been asked to implement this feature. I no longer have my
notes from my first foray, but I do recall that the phrase used is "auditing
trigger".

Here's the short version:
I have several tables in a SQL Server 2000 database, but one table in
particular is the one I am focused on. It has maybe 20 fields. In theory,
any row in this table should be entered once and never changed. However, I
did find it necessary to build a UI to change whatever was entered the first
time. So since it can be changed, I want to have a way of knowing which
fields were changed, when the changes occurred, who did the change, and the
before and after values for each field.

The obvious way to do this is to build another table, and record things
there. But I wondered if SQL Server 2000 has anything built in to keep track
of these things.

For the record, the app which enters and changes items in the database in an
ASP (classic) Intranet app.

TIA

Hi,
you could try:

--Set up the tables
create table Audit (TableName varchar(128), FieldName varchar(128), OldValue
varchar(1000), NewValue

varchar(1000))
go
create table trigtest (i int not null, j int not null, s varchar(10), t
varchar(10))
go
alter table trigtest add constraint pk primary key (i, j)
go

create trigger tr_trigtest on trigtest for insert, update, delete
as

declare @bit int ,
@field int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000)

select @TableName = 'trigtest'

select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME
+ ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where  pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
  raiserror('no PK on table %s', 16, -1, @TableName)
  return
end

select @field = 0
while @field < (select max(colid) from syscolumns where id =
(object_id('trigtest')))
begin
  select @field = @field + 1
  select @bit = (@field - 1 )% 8 + 1
  select @bit = power(2,@bit - 1)
  select @char = ((@field - 1) / 8) + 1
  if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
  begin
   select @fieldname = name from syscolumns where colid = @field and id =
object_id('trigtest')
   select @sql =   'insert Audit (TableName, FieldName, OldValue, NewValue)'
   select @sql = @sql +  ' select ''' + @TableName + ''''
   select @sql = @sql +  ',''' + @fieldname + ''''
   select @sql = @sql +  ',convert(varchar(1000),d.' + @fieldname + ')'
   select @sql = @sql +  ',convert(varchar(1000),i.' + @fieldname + ')'
   select @sql = @sql +  ' from #ins i full outer join #del d'
   select @sql = @sql +  @PKCols
   select @sql = @sql +  ' where i.' + @fieldname + ' <> d.' + @fieldname
   select @sql = @sql +  ' or (i.' + @fieldname + ' is null and  d.' +
@fieldname + ' is not null)'
   select @sql = @sql +  ' or (i.' + @fieldname + ' is not null and  d.' +
@fieldname + ' is null)'

   exec (@sql)
  end
end
go


insert trigtest select 1,1,'hi', 'bye'
insert trigtest select 2,2,'hi', 'bye'
insert trigtest select 3,3,'hi', 'bye'
update trigtest set s = 'hibye' where i <> 1
update trigtest set s = 'bye' where i = 1
update trigtest set s = 'bye' where i = 1
update trigtest set t = 'hi' where i = 1
select * from Audit
select * from trigtest

go
drop table Audit
go
drop table trigtest
go

Show quoteHide quote

Whoa!! That seems overly complex...

SQL Server has on inherent audit process. We 'roll our own'.

An easier to understand and manage process...

1. Make a duplicate structured Audit table for the Audit trail.
      (Could be in a different database, or even a different server.)
2. If the original has an identity field, in the Audit trail table
      make it just an int, NO identity property
3. Add three columns to the Audit table, ActionType (varchar(10))
      AuditDate (datetime) and LastUser (varchar(50))
4. Create a Trigger FOR UPDATE, DELETE
5. In the Trigger, first, RETURN if @@ROWCOUNT = 0
      (nothing done, don't run Trigger code.)
6. Do something like the following in the Trigger:

   Create a variable, @Action
   IF rows exists in inserted,
      set @Action = 'UPDATE'
   ELSE
      set @Action = 'DELETE'

   INSERT INTO MyAuditTable
      SELECT
           deleted.*
         , @Action   
         , getdate
         , system_user
      FROM deleted

7. Rollback if the INSERT fails, no audit trail, no changes.

This should provide you an audit copy of the previous row, when it was changed, and who changed it.

Show quoteHide quote

Middletree wrote:
Show quoteHide quotehttp://www.nigelrivett.net/AuditTrailTrigger.html

Duplicate rows Headach
Composite primary key on temp table.
How do I add foreign key to a table?
JOIN QUERY
'Transaction was deadlocked