"Jeff Cochran" <jeff.nospam@zina.com> wrote in message
news:413638f4.579828018@msnews.microsoft.com...
> I have a small ASP app on our intranet that handles the internal
> contact list. Nothing special, some tables for the department and
> employee details. I now have a request for providing a history of
> changes.
>
> Currently I capture the user ID making changes and the time, and store
> them in fields in the record. This came about because of the (I guess
> inevitable) question "Who changed that?" Now I need to keep a
> history/transaction log of changes so I can answer the question "Who
> changed that before the guy who changed it before the current change
> was made...?"
>
> I've dealt with transaction records for a retail operation years ago
> (DBase IV), and I don't want to get that involved in this. I'm not
> dumb enough to add fields for the last three changes to the records
> though. So I'm looking for suggestions on how others might handle
> this.
>
> So far I only need to do this on the employee details, so I'm thinking
> something like:
>
> Current tblEmployeeDetails structure (SQL 2000):
>
> EmployeeID, IDENTITY, PK
> EmployeeFirstName
> EmployeeLastName
> EmployeeDepartment, FK
> EmployeePhone
> EmployeeCell
> EmployeeLocation
> LastUpdateTime
> LastUpdateUser
>
> I'm thinking of dropping the LastUpdate fields and creating a
> Transaction History table, as:
>
> EmployeeID, IDENTITY, PK
> EmployeeFirstName
> EmployeeLastName
> EmployeeDepartment, FK
> EmployeePhone
> EmployeeCell
> EmployeeLocation
> UpdateTime
> UpdateUser
>
> Then I just need to insert a record each time a change is made to the
> employee details table. My current skillset would be to just use a
> second Query for this insert, but I assume a trigger or making the
> entire insert/update part of a stored procedure would be a better
> option. I've been going through the code for the Northwind database
> for examples and should be able to muddle through (with occasional
> help from the SQL group...).
>
> Does this make sense to approach it this way? Is there a reason to
> use ASP for the entire process and not stored procedures? Other than
> it's a learning curve for me to get a handle on the stored procedures
> and possibly triggers?
>
> Thanks,
>
> Jeff

Jeff Cochran wrote:
> I have a small ASP app on our intranet that handles the internal
> contact list. Nothing special, some tables for the department and
> employee details. I now have a request for providing a history of
> changes.
>
> Currently I capture the user ID making changes and the time, and store
> them in fields in the record. This came about because of the (I guess
> inevitable) question "Who changed that?" Now I need to keep a
> history/transaction log of changes so I can answer the question "Who
> changed that before the guy who changed it before the current change
> was made...?"
>
> I've dealt with transaction records for a retail operation years ago
> (DBase IV), and I don't want to get that involved in this. I'm not
> dumb enough to add fields for the last three changes to the records
> though. So I'm looking for suggestions on how others might handle
> this.
>
> So far I only need to do this on the employee details, so I'm thinking
> something like:
>
> Current tblEmployeeDetails structure (SQL 2000):
>
> EmployeeID, IDENTITY, PK
> EmployeeFirstName
> EmployeeLastName
> EmployeeDepartment, FK
> EmployeePhone
> EmployeeCell
> EmployeeLocation
> LastUpdateTime
> LastUpdateUser
>
> I'm thinking of dropping the LastUpdate fields and creating a
> Transaction History table, as:
>
> EmployeeID, IDENTITY, PK
> EmployeeFirstName
> EmployeeLastName
> EmployeeDepartment, FK
> EmployeePhone
> EmployeeCell
> EmployeeLocation
> UpdateTime
> UpdateUser
>
> Then I just need to insert a record each time a change is made to the
> employee details table. My current skillset would be to just use a
> second Query for this insert, but I assume a trigger or making the
> entire insert/update part of a stored procedure would be a better
> option. I've been going through the code for the Northwind database
> for examples and should be able to muddle through (with occasional
> help from the SQL group...).
>
> Does this make sense to approach it this way? Is there a reason to
> use ASP for the entire process and not stored procedures? Other than
> it's a learning curve for me to get a handle on the stored procedures
> and possibly triggers?
>
> Thanks,
>
> Jeff

<mdkersey@hal-pc.org> wrote:

>Jeff Cochran wrote:
>> I have a small ASP app on our intranet that handles the internal
>> contact list. Nothing special, some tables for the department and
>> employee details. I now have a request for providing a history of
>> changes.

>Take a look at
>Time After Time (SQL Audits and Historic Views)
>http://www.standardreporting.net/survival/view.aspx?_@id=53431
>which outlines techniques for creating audit trails.