Building an Audit Trail for Your Data - Passing Table Names
(Page 3 of 6 )
A table name is retrieved from the first cursor. This table name is then passed as a parameter to the second cursor which obtains column information from the data dictionary. Some data type checking is required so the proper length can be specified, or, in the case of NUMBER, a precision and scale.
To audit the data, three columns are added to the audit table: aud_action captures the type of DML performed, aud_timestamp receives the system date, and aud_user records the current user performing the action.
The resulting build_script can be immediately executed from the current script.
SPOOL OFF
@build_audit_tables.sql
If the schema contained the single unaudited table described below:
| desc audit_test | | |
| Name | Null? | Type |
| TEST_VARCHAR2 | | VARCHAR2(30) |
| TEST_CHAR | | CHAR(4) |
| TEST_LONG | | LONG |
| TEST_NUMBER | | NUMBER(2 |
| TEST_NUMBER2 | | NUMBER |
| TEST_DATE | | DATE |
The script build_audit_tables.sql will look something like this:
| CREATE TABLE audit_test$AUD( | |
| test_varchar2 | VARCHAR2(30), |
| test_char | CHAR(4), |
| test_long | LONG, |
| test_number | NUMBER(2,0), |
| test_number2 | NUMBER(38,0), |
| test_date | DATE, |
| aud_action CHAR(3),aud_timestamp DATE, | aud_user VARCHAR2(30))/
|
Next: Creating the Audit Triggers >>
More Database Articles
More By Michael Kleane
