Below is a table audit setup.
Without changing any table or field names and rather customized by me from
the
basic original, but I hope you'll get the idea; in MSSQL
create the audit table
==============================================
/****** Object: Table [dbo].[ALLCONTACTSHISTORY] Script Date: 04/07/2008
19:19:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ALLCONTACTSHISTORY](
[AUDITREF] [int] IDENTITY(1,1) NOT NULL,
[AUDITTYPE] [char](1) NOT NULL,
[COLUMN_NAME] [varchar](128) NOT NULL,
[USERREF] [int] NOT NULL,
[OLDVALUE] [varchar](8000) NULL,
[NEWVALUE] [varchar](8000) NULL,
[UPDATED] [datetime] NOT NULL,
[BYWHO] [varchar](128) NOT NULL,
CONSTRAINT [PK_ALLCONTACTSHISTORY] PRIMARY KEY CLUSTERED
(
[AUDITREF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/*
======================================
create the trigger on ALLCONTACTS which fills the audit table
======================================
*/
/****** Object: Trigger [dbo].[TR_AUDIT_ALLCONTACTS] Script Date:
04/07/2008 19:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[TR_AUDIT_ALLCONTACTS] on [dbo].[ALLCONTACTS] FOR
INSERT, UPDATE, DELETE
AS
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSelect varchar(1000),
@jobref int,
@doIt bit
set @TableName = 'ALLCONTACTS'
set @doIt = 1
-- date and user
SELECT @UserName = ISNULL(BYWHO,'Unknown') FROM inserted
select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' +
convert(varchar(12), getdate(), 114)
-- Special username 'SysAdmin' to NOT insert anything into audit
table
if @UserName = 'SysAdmin'
set @doIt = 0
-- Action
if @doit = 1
BEGIN
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'
-- get list of columns
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, @maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and
ORDINAL_POSITION > @field
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 or @Type
in ('I','D')
begin
select @fieldname = COLUMN_NAME from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and
ORDINAL_POSITION = @field
select @sql = 'insert ALLCONTACTSHISTORY
(AUDITTYPE, USERREF,COLUMN_NAME, OLDVALUE, NEWVALUE, UPDATED, BYWHO)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ', i.USERREF'
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ', ISNULL(CASE WHEN
len(convert(varchar(1000),d.' + @fieldname + ')) = 0 THEN ''[blank]'' WHEN
ISDATE(convert(varchar(20),d.' + @fieldname + '))=1 THEN
convert(varchar(17),d.' + @fieldname +', 113) ELSE
convert(varchar(1000),d.' + @fieldname + ') END,''[blank]'')'
select @sql = @sql + ', ISNULL(CASE WHEN
len(convert(varchar(1000),i.' + @fieldname + ')) = 0 THEN ''[blank]'' WHEN
ISDATE(convert(varchar(20),i.' + @fieldname + '))=1 THEN
convert(varchar(17),i.' + @fieldname +', 113) ELSE convert(varchar(1000),i.'
+ @fieldname + ') END,''[blank]'')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
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)
--PRINT @sql
end
end
END
go
===============================================
Regards
Richard
> -----Original Message-----
> From: Phill B [mailto:[EMAIL PROTECTED]
> Sent: 07 April 2008 17:30
> To: CF-Talk
> Subject: Re: Can you guys give me your thoughts on this?
>
> Very true. The problem I'm having is that I cant figure out
> how to do this in a database. I've never worked with a
> database driven application that stores revisions that I can
> dig through the code to see how they do it.
> Plus, I couldn't find any good articles or books on this sort
> of thing.
> That's why I'm asking for help. I know there has to be a
> better way. I just don't have the experience to pull from to
> figure out what it is.
>
> On Mon, Apr 7, 2008 at 11:17 AM, Dave Watts wrote:
>
> > > The product will be stored in the database but all the
> versions of
> > > it would be stored on the server in XML with the
> transactions being
> > > recorded in it.
> >
> > Why not store that in the database? That's what databases
> are for. XML
> > is better for transport between systems, databases are
> better for storage.
> >
> > Dave Watts, CTO, Fig Leaf Software
> >
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302891
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
