Archives of the TeradataForum

Message Posted: Fri, 08 May 2009 @ 10:04:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I've successfully used triggers to perform this function.

For our application we used two tables - the base table and the audit table.

The definition of the base table and audit tables were identical except that we added operation, operation_by and operation_ts columns to the audit table.

We used before image delete and update triggers only so the base table always had the "current" row. The audit table had the previous image.

For our application (a web based application using connection pools) we put "updated_by" and "updated_ts" columns on the base table so that we could capture the actual user id of the user making the change. This user id is the id used by the user to log on to the application and is *not* visible to Teradata.

The operation_by and operation_ts were set by default values defined in the audit table definition (user & current_timestamp). I won't go into details here, but if you do not put the "operation_by" and "operation_ts" columns in the audit table, you *will* risk getting duplicate row errors when your users work within your application.

The only real disadvantage to this is that if you do lots of updates and your audit table's NUPI is the same as the base table (single amp operation) you will get lots of hash collisions. This could adversely affect performance as your history grows. We haven't observed this problem because our quantity of updates per audited object is low (e.g. lots of accounts are updated, but for any one account there are very few updates).

This model has the advantage that you can easily view the entire history of the table by querying the current record then appending the audit records ordered by operation_ts desc.

Hope that helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>