Storing date & time and updater/user of row data in Oracle Apex

Question

Looking for an ideal way to store (1) date & time of update (2) updater/user per row of table data using Oracle Apex. I am thinking of adding 2 extra columns to store the info and trying to come up with a good as to how changes per row can be tracked.

Answer 1

If you want create logs of insert, update , delete on your table, adding 2 columns not enough. Each new update will erase previous and delete couldn't be logged. So you need to store log table separately from data table, and fill it on before and after triggers created on your data table. If you want sample I can provide some.

Here simplified example, of course in real life data will be more complex and I guess a trigger should be more smarter, but this is a simple start point to create your own. After executing codes below, try to insert, update, delete delete records in table TEST_DATA, see what happens in TEST_LOG

Create data table

create table TEST_DATA (
  UNID number,
  COL_B varchar2(50)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST_DATA
  add constraint PK_TEST_DATA_UNID primary key (UNID);

Create log table for it

create table TEST_LOG (
  UNID number,
  OPERATION varchar2(1),
  COL_OLD varchar2(50),
  COL_NEW varchar2(50),
  CHNGUSER varchar2(50),
  CHNGDATE date
);

and finally create trigger which tracks changes

create or replace trigger TR_LOG_TEST_DATA
   after update or insert or delete on TEST_DATA
   referencing new as new old as old
   for each row
begin
   if Inserting then
      insert into TEST_LOG
         (UNID, OPERATION, COL_OLD, COL_NEW, CHNGUSER, CHNGDATE)
      values
         (:new.unid, 'I', null, :new.col_b, user, sysdate);
   end if;

   if Updating then
      insert into TEST_LOG
         (UNID, OPERATION, COL_OLD, COL_NEW, CHNGUSER, CHNGDATE)
      values
         (:new.unid, 'U', :old.col_b, :new.col_b, user, sysdate);
   end if;

   if Deleting then
      insert into TEST_LOG
         (UNID, OPERATION, COL_OLD, COL_NEW, CHNGUSER, CHNGDATE)
      values
         (:old.unid, 'D', :old.col_b, null, user, sysdate);
   end if;

end;