Configure a Table for Auditing v2.0

Auditing capabilities are available for ANY table within the SDW without ANY CHANGES required to the source table. A generic trigger will capture any inserts, updates, deletion or truncation of the data within a table configured for capture.  The existing data (original) along with the new data are stored in the logged actions table within the audit schema.  

Information stored for each row change includes:

1. Unique identifier for each auditable event 
2. Database schema audited table for this event is in
3. Non-schema-qualified table name of table event occurred in 
4. Login / session user whose statement caused the audited event 
5. Transaction start timestamp for tx in which audited event occurred 
6. Statement start timestamp for tx in which audited event occurred
7. Application name set when this audit event occurred. Can be changed in-session by client.
8. Action type; I = insert, D = delete, U = update, T = truncate
9. Record value. Null for statement-level trigger. For INSERT this is the new tuple. For DELETE and UPDATE it is the old tuple. 
10. New values of fields changed by UPDATE. Null except for row-level UPDATE events. 
11. 't' if audit event is from an FOR EACH STATEMENT trigger, 'f' for FOR EACH ROW

By having access to all this information it is possible to rebuild tables of data from a specific time.

Configure a table for auditing

Use the following to set up a table for auditing. You would need to run this statement (as admin user) for every table you wish to audit.

SELECT audit.audit_table('schema_name.table_name');