[thelist] Database history - handling references to rows whichdon't exist any more?

UIT DEV UITDEV at GMAIL.COM
Fri Apr 24 08:46:50 CDT 2009


Jay,  I have bookmarked this article:
http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html
and I adopted it to my needs.  Hopefully this can help you.  It may be the
article you referred to, dont know...

HTH
-Tony

On Fri, Apr 24, 2009 at 00:29, Jay Turley <jayturley at gmail.com> wrote:

> Thanks, Chris! For now it's just #1, so the isdeleted field will work
> perfectly.
> After treading everything you wrote, it reminded me of an article I had
> read
> once about journaling databases, where you keep track of every change made
> ever! I'm SO glad I don't have to create something like that. Whew!
>
> -Jay
>
> On Thu, Apr 23, 2009 at 4:58 PM, Chris Anderson <Chris at activeide.com>
> wrote:
>
> > > If the user adds or deleted a new item to the database (among other
> > > actions)
> > > using the application, we store that information in our history table.
> > >
> > ...
> > > Therefore we are saving the primary key of the items in our history
> > > table
> > > like so:
> > ..
> > >
> > > This is because I am building links pointing to the items on the fly,
> > > i.e.
> > > if there IS an item_id, then I pull the item info from the database
> > > using
> > > that item_id and then output HTML like
> >
> > 1.Do you need to show the previously deleted items?
> > 2.Do you need to show the state of a record at any point in time?
> >
> > If 1 but not 2, just add a "IsDeleted" field and when reading, include
> > "IsDeleted = 0" to the where clause
> > (It's a good idea to use a view to do this and always select from the
> > view - then you'll not forget)
> > Use the isDeleted field to determine how you present the output
> >
> > If 1 and 2 create a mirrored schema eg
> >
> > TableA:
> >        refid int not null identity(1,1),
> >        someField int not null,
> >        someOtherField int null
> >
> > TableAAudit:
> >        auditId int not null identity(1,1),
> >        changeDate datetime not null,
> >        changeType int not null (or char(1) not null if prefered)
> >        refid int not null,
> >        someField int not null,
> >        someOtherField int null
> >
> > where:
> > auditId is just there to provide a point of reference and uniqueness
> > changeDate is the date/time of the change
> > changeType is the type of change (create, update, delete)
> > refid is the PK of the record in TableA
> > someField is the new value of someField (null if record deleted)
> > someOtherField is the new value of someOtherField (null if record
> > deleted)
> >
> > Note that even though someField was not null in TableA, it's nullable in
> > the TableAAudit table. This is to allow you to store null for when the
> > record is deleted.
> >
> > Finally add a trigger to TableA and store the new data in TableAAudit
> >
> > To find the state of a record at a particular time, simply find the
> > latest record in the audit table before that time (i.e. SELECT TOP 1
> > .... ORDER BY changedate DESC)
> >
> > The fun starts if you want to tie together changes that occur within a
> > single transaction (eg adding an order header and 5 lines) and reporting
> > those as a single atomic operation.
> > (It's possible based on the latter approach, but a little more involved)
> >
> > Chris
> > --
> >
> > * * Please support the community that supports you.  * *
> > http://evolt.org/help_support_evolt/
> >
> > For unsubscribe and other options, including the Tip Harvester
> > and archives of thelist go to: http://lists.evolt.org
> > Workers of the Web, evolt !
> >
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list