Maintain History

Hi All
I have a table that contains details regarding branch offices and as these
offices regularly change details such as the branch name and address I need
to always have the current details and also maintain a history of historical
details for each branch office. Initially I was think of having a duplicate
table with a datetime column to store when the last change was made and also
a column that contained the details of who changed the branch office details.
I was going to control this through the application but have identified that
there is any additional vendor application that also change details in this
table and the application can not be modified.
Is the best way to maintain a history of branch office details in this
scenario to create a trigger on the table and populate a duplicate table with
the details of the branch office as they where before being modified or is
there a better way to do this?
Thanks
- DavidHi David
There is a simple auditing example (E) in the Create Trigger topic in books
online. There may be an impact on the system if you implement these on all
your tables, in both the amount of storage used and time taken to make
changes. To reduce the performance degredation it is best to keep the trigger
as simple as you can , if any reconciliation is necessary then you can do
that during a quiet period of possibly offline.
Alternatively you can purchase a log reading program, you may want to log at
LogExplorer from Lumigent www.lumigent.com or LogPI which has been aquired
by Goldengate see www.logpi.com
John
"David" wrote:
> Hi All
> I have a table that contains details regarding branch offices and as these
> offices regularly change details such as the branch name and address I need
> to always have the current details and also maintain a history of historical
> details for each branch office. Initially I was think of having a duplicate
> table with a datetime column to store when the last change was made and also
> a column that contained the details of who changed the branch office details.
> I was going to control this through the application but have identified that
> there is any additional vendor application that also change details in this
> table and the application can not be modified.
> Is the best way to maintain a history of branch office details in this
> scenario to create a trigger on the table and populate a duplicate table with
> the details of the branch office as they where before being modified or is
> there a better way to do this?
> Thanks
> - David