Oracle Apex - Best way to send Email Interactive Grid-CodePudding

I have an Interactive Grid on my page, the idea is that every time the user inserts / updates rows an email will be send to the Admins for audit checks (manual back-office process). This is the code (Trigger) I have so far for the insert, however, it is not the best option, since it only captures the last - new - Id:

create or replace trigger "my_email_trigger"                
  after insert on "my_table"               
  for each row  

DECLARE 
l_body CLOB;
v_user varchar2(300);
V_REQUESTER varchar2(300);
v_max number;

begin   

l_body := 'Hi ' || INITCAP(replace(regexp_replace(:NEW."User_email",'@[a-zA-z0-9.]*',''), 
'.', ' ')) ||',' || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body || 'Please review new Row ID: '|| :NEW."ID" ||utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body || 'Thank you' || utl_tcp.crlf;

apex_mail.send(
    p_to   => :NEW."User_email",
    p_from => '[email protected]',
    p_body => l_body,
    p_subj => 'Please review new Row Id' );

 END;

If the user adds 3 new rows, this trigger would send 3 different emails. Is there a way to capture all 3 inserted rows (for example) on a single email?

Thanks!

CodePudding user response：

Instead of using a trigger on the table - a trigger will fire for every affected row and that will not work - I suggest you use a page process. Check this post which is pretty similar. Note that the page process is also fired for every row so you'll probably need 2 page processes

One page process on the IG Editable region to populate an item with the list of affected row ids. Since this is executed for every affected row you could just concatenate values and this process cannot send the email because you'd have the same issue as the trigger: one mail per row.
Another page process that just sends the email if the page item from the first process is not null.

Another advantage over trigger is that this keeps you business functionality in the application code. If you move that to triggers isn't that visible anymore.