Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 8th, 2004, 02:34 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default History data

Dear all,

I'm developing an internet application that uses MS-SQL server as a database.
The database contains customer information. The customer is able to create, update and detele records via forms in the application.

When a customer deletes a record I want to keep some history of the deleted record.

For the moment when a customer deletes a record the record is in fact not deleted but I update a field called "enddate" which I fill with the sysemdate.
Next time when the customer reads the information the select query will not select the "deleted" records because in my query I put "where enddate is null."

Would it be better to work with tables that have the same naming as my real data table but with an extention _HIST ?
So when a customer deletes a record it is actually deleted but via triggers inserted in the table with extention _HIST …

It concerns about 20 tables … (This will result in 20 history tables)

Any other suggestions to implement some history tracking ??

Reply With Quote
  #2 (permalink)  
Old December 8th, 2004, 07:05 AM
lsf lsf is offline
Registered User
 
Join Date: Dec 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe you could use the log files??


Reply With Quote
  #3 (permalink)  
Old December 8th, 2004, 09:57 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

You can create a table to track all changes to your database by storing the values that have changed.

Here is a sample scripts that may give you all that you need

Code:
/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/

--Set up the tables
if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
go
create table Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
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 ,
    @maxfield int ,
    @char int ,
    @fieldname varchar(128) ,
    @TableName varchar(128) ,
    @PKCols varchar(1000) ,
    @sql varchar(2000), 
    @UpdateDate varchar(21) ,
    @UserName varchar(128)

    select @TableName = 'trigtest'

    -- date and user
    select     @UserName = system_user ,
        @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

    -- 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
        begin
            select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
            select @sql =         'insert Audit (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName)'
            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 +     ',''' + @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)
        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
Jaime E. Maccou
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Browser history abdul_owiusa Javascript How-To 11 October 13th, 2006 09:13 AM
How to clear history toshi Javascript 0 August 17th, 2006 06:07 AM
history.back(-1); elania Javascript How-To 3 February 2nd, 2005 03:14 PM
Window.History pvasudevan Javascript 5 September 4th, 2004 03:49 AM
Record History cdenequolo Classic ASP Basics 2 December 3rd, 2003 12:44 PM



All times are GMT -4. The time now is 01:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.