use triggers to keep history of relational tables

say I have 6 tables.

1. Workstation
2. Workstation_CL
3. Location
4. Location_CL
5. Features
6. Features_CL

I am currently using triggers to do inserts into the "_CL" version of each table with an additional field that denotes whether the change was an "UPDATE", "INSERT" or "DELETE".

the workstation table keeps track of the "modifi开发者_如何学Goed_by" user. if a user updates the location of a "Workstation" object, the "Location" table gets updated as well as the "Workstation" table. the only modification to the Workstation table is the "modified_by" field so that I will know who made the change.

The problem I am having is when I think about pulling an audit report. How will I link records in the "Location_CL" to the ones in the "Workstation_CL" both are populated by separate triggers.

somehow my question portion was erased. sorry about that.

Question: how can I pull some type of unique identifier to have in both the "Workstation_CL" and the "Location_CL" so that I can identify each revision? for instance, when I pull all records from the "Location_CL" and I see all location changes, pulling the username from the "Workstation_CL" that made the location change?

Give each revision a GUID generated by the trigger. Populate a field (RevisionId) in both tables with the value.

You need 2, maybe 3 columns on each audit table.

1) Timestamp, so you know when the changes were made.
2) User changed, so you can track who made the changes - I assume that Location can change independently of Workstation. 3) You might need an identifier for the transaction, too. I THINK you can get an id from the DB, though I'm not sure.

I don't think you can have an effective report without timestamps and users, though, and I don't think you just have the user on one table.

During the trigger event, I was able to exec the following:

SELECT @trans_id=transaction_id FROM sys.dm_tran_current_transaction

which gives me the transaction id for the current operation.

with that, I am able to insert it in to the corresponding _CL table and then perform selects that will match the auto-gen id's.