My work with temporal data definitely backs up the claim that it makes it easy to add an audit trail to existing tables with no change to application software, but it's only addressing 1/2 my problem - I can now track what's been changed, and when it was changed, but I need to keep track of who made the change (again with little or no change to existing application software).
We are an IMS/DC shop, so there are a large number of programs (and users) modifying our DB2 tables. Even were we to insert/update a user ID column in our tables, it would not reflect the user who "pushed" the old row into the history table. Such a column would always lag behind - the user who made the change would be identified in the current row and not the history table. Nor would be able to track a deletion.
Is there some way to define a special register's value, such as CURRENT CLIENT_USER_ID, to be generated into the history table's defintion such as the timestamps are?
Pinned topic OK v10 answered "when"? Now what about "who"?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-03-21T14:06:50Z at 2012-03-21T14:06:50Z by mitchC
Steve_Chen 060000XBJ53 Posts
Re: OK v10 answered "when"? Now what about "who"?2012-03-16T00:02:32ZThis is the accepted answer. This is the accepted answer.One possible solution is to create a before insert trigger directly on the each of the history table,such as
CREATE TRIGGER AUTHAUDIT
NO CASCADE BEFORE INSERT ON POLICY_HISTORY
REFERENCING NEW AS NROW
FOR EACH ROW MODE DB2SQL
SET USER_ID_COL=CURRENT CLIENT_USERID ;
In this example, there is a column USER_ID_COL defined for system period temporal table POLICY and its associated history table POLICY_HISTORY for auditing purpose.
Re: OK v10 answered "when"? Now what about "who"?2012-03-19T12:43:36ZThis is the accepted answer. This is the accepted answer.
- Steve_Chen 060000XBJ5
Re: OK v10 answered "when"? Now what about "who"?2012-03-21T14:06:50ZThis is the accepted answer. This is the accepted answer.
- mitchC 27000560GC