community.borland.com

Article #25586: An example of logging user activity against tables

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