Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ana.

Asked: April 07, 2021 - 7:36 am UTC

Last updated: April 14, 2021 - 9:44 am UTC

Version: 19.2

Viewed 100+ times

You Asked

I am in the process of implementing a process in APEX. This process includes some roles. The process is initiated by a person who fills in forms in the first step.
The data entered is stored in collections and entered into the corresponding tables after submission. This also initiates the next step of the process.

Now to my question, I would like to display a kind of history of the completed steps after the completed forms have been submitted (i.e. after the first process step has been completed).
I was thinking of a kind of log table.
This summarises which action has been taken, who has taken the action, and what other important information has been entered in the table (e.g. which person responsible was selected in the form, etc., the ID of the table).
-- create log tbl
create table form_submitted
log_id number not null,
Responsible_id1 number ,
Responsible_id2 number,
Responsible_id3 number,
Responsible_id4 number,
added_date date,
action varchar2(100);

alter table form_submitted add constraint pk_log_id PRIMARY KEY (log_id);
-- create sequence to autoincrement values
create sequence seq_log_id
start with 1
increment by 1
nocache
nocycle;
--create trigger to insert auto-incremented value
create or replace trigger bi_form_submitted
before insert 
on form_submitted
for each row 
begin
:new.log_id :=seq_log_id.nextval;
end;

To achieve this, I thought of creating a trigger that would track the user's activities and enter the necessary information into the log table.
-- create trigger to track user activity and insert on logs table
create or replace trigger trg_user_activity
after insert or update or delete
on tbl1

for each row 
begin
  if deleting then 
  --insert data in tbl_logs
  insert into form_submitted
  (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action)
  values
    (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_deleted');
end if;
if updating then 
  --insert data in tbl_logs
  insert into form_submitted
   (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action)
  values
  (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_updated');

end if;
if inserting then 
  --insert data in tbl_logs
  insert into form_submitted
   (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action)
  values
    (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_submitted');

end if;
end;

This table I want then to display as a classic report in the history tab.
Each time a user makes a change it will be logged and displayed. But each time in a different log tables. This means in the second step, another user has to make an action, this should as well be logged and been displayed in the history tab.
The aim is to log and display every activity of each user in the process.

I would like some help with the following three things:

1. how can I get the information I need for the log table( like Responsible_id1 ) from the other table (here tbl1).
2. different users edit the same table (which means that the next user adds data to the same table) but a different entry has to be made in a different log table. Can I bind the trigger to a user or to this specific process step.
3.After the first process step has been successfully completed, how can I display the current status (this means that the steps should not have to be carried out again the next time the user logs in but he should see in which step of the process he is at the moment)?

A few suggestions would already be very helpful.
I am relatively new to APEX and not very familiar with the development.
I am also not sure whether a trigger is the right common solution for such a use case.


and we said...

I'm a little confused by the example - is FORM_SUBMITTED the log table or main table? This makes it tricky to give detailed advice here.

1. How does Responsible_id1 and other values relate to those in the source table?

If they're columns in the triggering table, you can access their values using :new/:old as appropriate (you need :old values when deleting). If they're not, we need more detail on how they're related to help here.

2. You can have an if/case statement that checks the user and inserts into the appropriate table.

But this sounds like you have a log table per user. I would advise against this - it'll make development harder. Have one table with a user column and store that.

3. Please clarify what exactly you're trying to achieve here. It sounds like you want to find the latest row for a user/process, which you can do with a query like:

with rws as (
  select ..., 
         row_number () over (
           order by update_time desc
         ) rn
  from   ...
  where  ...
)
  select * from rws
  where  rn = 1;


There are other ways; read about these at:

https://blogs.oracle.com/sql/how-to-select-the-top-n-rows-per-group-with-sql-in-oracle-database#one-row-per-group

That said, I would avoid triggers as much as possible and build a PL/SQL API instead, e.g.:

create or replace package form_submission_mgr as 
  function insert_form ( ... ) 
    return table.id%type;
  procedure update_form ( ... );
  procedure delete_form ( ... );
end;
/


These will insert/update/delete from the main table as needed and log at the same time. Then hook up your form submission process to call the appropriate procedure.

Finally, if you want auto-incrmenting PK values, there's no need for a trigger doing this:

:new.log_id :=seq_log_id.nextval;


Either declare the column as an identity or set the sequence to be the column's default.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.