Problem: Is it possible to log user activity against a table. Solution: Yes. Please see example below: connect log_user_activity.gdb user "sysdba" password "masterkey"; drop database; create database "log_user_activity.gdb" user "sysdba" password "masterkey"; set auto off; set time; create table test_logging ( id integer, test_value varchar(10) ); create table log_inside_trans_control ( id integer, which_user varchar(15), what_time date, what_action varchar(10), what_table varchar(31) ); create table log_outside_trans_control external "d:templog_table.txt" ( id integer, which_user varchar(15), what_time date, what_action varchar(10), what_table varchar(31) ); commit; create generator gen_counter; create generator gen_log_trans; commit; set term ^^; create trigger trig_gen_counter for test_logging before insert position 1 as begin new.id = gen_id(gen_counter, 1); end^^ create trigger trig_log_insert for test_logging before insert position 2 as declare variable var_id integer; begin var_id = gen_id(gen_log_trans, 1); insert into log_inside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'INSERT', 'TEST_LOGGING'); insert into log_outside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'INSERT', 'TEST_LOGGING'); end^^ create trigger trig_log_update for test_logging before update position 3 as declare variable var_id integer; begin var_id = gen_id(gen_log_trans, 1); insert into log_inside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'UPDATE', 'TEST_LOGGING'); insert into log_outside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'UPDATE', 'TEST_LOGGING'); end^^ create trigger trig_log_delete for test_logging before delete position 4 as declare variable var_id integer; begin var_id = gen_id(gen_log_trans, 1); insert into log_inside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'DELETE', 'TEST_LOGGING'); insert into log_outside_trans_control (id, which_user, what_time, what_action, what_table) values (:var_id, USER, 'now', 'DELETE', 'TEST_LOGGING'); end^^ set term ;^^ commit; insert into test_logging (test_value) values ("testing"); commit; update test_logging set test_value = "new_value" where id = 1; commit; delete from test_logging where id = 1; commit; select * from log_outside_trans_control; select * from log_inside_trans_control; commit;
Last Modified: 26-OCT-00