ad_audit_trail
and
ad_audit_trail_for_table
) that helps you display the old
values of a row, including highlighting particular changed columns, (3)
a Tcl procedure (ad_audit_delete_row
) that simplifies the
logging of a deleted row, and (4) an example user interface (
audit-tables
, audit-table
, audit
) to retrieve and display audit histories.
We distinguish between the on-line transaction processing (OLTP) tables that are used in the minute-by-minute operation of the server and the audit tables.
Here are the steps to add audit trails:
create table ec_products ( product_id integer not null primary key, product_name varchar(200), one_line_description varchar(400), ... -- the user ID and IP address of the last modifier of the product last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null );
create table ec_products_audit as select * from ec_products where 1 = 0; alter table ec_products_audit add ( delete_p char(1) default('f') check (delete_p in ('t','f')) );
Note that it is not possible to automatically populate the audit table on deletion because we need the IP address of the deleting user.create or replace trigger ec_products_audit_tr before update or delete on ec_products for each row begin insert into ec_products_audit ( product_id, product_name, one_line_description, ... last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.product_name, :old.one_line_description, ... :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors
ad_audit_delete_row
with args key list, column
name list, and audit_table_name. This procedure calls
ad_get_user_id
and ns_conn peeraddr
and records
the user_id and IP address of the user deleting the row.
ns_db dml $db "begin transaction" ns_db dml $db "delete from ec_products where product_id=$product_id" ad_audit_delete_row $db [list $product_id] [list product_id] ec_products_audit ns_db dml $db "end transaction"
ad_audit_trail
in an admin page to
show the changes made to a key. Insert a call to ad_audit_trail_for_table
to show the changes made to an entire table over a specified period of time.
ticket_pretty
and
ticket_pretty_audit
for an example. This has the
benefit of decoding the meaningless integer ID's and highlighting potential data
integrity violations.
ad_audit_trail_for_table
ad_audit_trail
ad_audit_delete_row