Oracle Apex交互式网格批量操作单邮件发送方案问询

在Oracle Apex页面的Interactive Grid中，要求用户插入/更新行时向管理员发送审计邮件。当前使用行级AFTER INSERT触发器实现插入时发邮件，但批量插入多行时会触发多次邮件发送，每封仅包含单条新增记录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 => 'noreply@mail.com',
    p_body => l_body,
    p_subj => 'Please review new Row Id' ); 

 END;

行级触发器会对每一行插入单独执行一次，因此批量插入时会多次调用apex_mail.send。要实现汇总邮件，需改用语句级触发器统一处理整个插入操作的所有记录，以下提供两种可行方案：

方案1：行级触发器收集数据 + 语句级触发器发送汇总邮件 #

步骤1：创建临时表存储新增记录信息 #

CREATE GLOBAL TEMPORARY TABLE temp_inserted_rows (
    id NUMBER,
    user_email VARCHAR2(300),
    created_date TIMESTAMP DEFAULT SYSTIMESTAMP
) ON COMMIT DELETE ROWS; -- 提交后自动清空表数据，保证会话隔离

步骤2：修改原行级触发器，仅收集数据不发邮件 #

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

BEGIN   
  -- 将新增行的关键信息存入临时表
  INSERT INTO temp_inserted_rows (id, user_email)
  VALUES (:NEW."ID", :NEW."User_email");
END;

步骤3：创建语句级触发器，汇总数据并发送邮件 #

create or replace trigger "my_email_trigger_stmt"                
  after insert on "my_table"               
  STATEMENT level -- 整个插入操作完成后仅执行一次

DECLARE 
  l_body CLOB;
  l_recipients VARCHAR2(300); -- 可固定管理员邮箱，或取临时表中收件人
  l_user_name VARCHAR2(300);
BEGIN   
  -- 避免无数据时触发邮件
  IF EXISTS (SELECT 1 FROM temp_inserted_rows) THEN
    -- 获取收件人（示例取第一条记录的邮箱，可按需调整）
    SELECT user_email INTO l_recipients FROM temp_inserted_rows WHERE ROWNUM = 1;
    -- 生成收件人名称（沿用原逻辑）
    l_user_name := INITCAP(replace(regexp_replace(l_recipients,'@[a-zA-z0-9.]*',''), '.', ' '));

    -- 构建邮件正文
    l_body := 'Hi ' || l_user_name || ',' || utl_tcp.crlf;
    l_body := l_body || utl_tcp.crlf;
    l_body := l_body || 'Please review the following new Row IDs:' || utl_tcp.crlf;
    l_body := l_body || '----------------------------------------' || utl_tcp.crlf;

    -- 遍历临时表，添加所有新增记录ID
    FOR rec IN (SELECT id FROM temp_inserted_rows ORDER BY id) LOOP
      l_body := l_body || '- Row ID: ' || rec.id || utl_tcp.crlf;
    END LOOP;

    l_body := l_body || utl_tcp.crlf;
    l_body := l_body || 'Thank you' || utl_tcp.crlf;

    -- 发送汇总邮件
    apex_mail.send(
        p_to   => l_recipients,
        p_from => 'noreply@mail.com',
        p_body => l_body,
        p_subj => 'Please review new Row IDs (Batch Insert)' ); 
  END IF;
END;

方案2：直接在语句级触发器中查询新增记录（依赖序列/时间戳） #

如果my_table的ID由序列生成，或存在记录插入时间的字段（如created_date），可直接在语句级触发器中查询本次批量插入的记录，无需临时表：

create or replace trigger "my_email_trigger_batch"                
  after insert on "my_table"               
  STATEMENT level

DECLARE 
  l_body CLOB;
  l_recipients VARCHAR2(300);
  l_user_name VARCHAR2(300);
  v_min_id NUMBER;
  v_max_id NUMBER;
BEGIN   
  -- 获取本次插入的ID范围（替换为你的序列名称）
  SELECT MIN("ID"), MAX("ID") INTO v_min_id, v_max_id FROM "my_table"
  WHERE "ID" >= (SELECT last_number - increment_by FROM user_sequences WHERE sequence_name = 'MY_TABLE_ID_SEQ');

  IF v_min_id IS NOT NULL THEN
    -- 获取收件人
    SELECT "User_email" INTO l_recipients FROM "my_table" WHERE "ID" = v_min_id;
    l_user_name := INITCAP(replace(regexp_replace(l_recipients,'@[a-zA-z0-9.]*',''), '.', ' '));

    -- 构建邮件正文
    l_body := 'Hi ' || l_user_name || ',' || utl_tcp.crlf;
    l_body := l_body || utl_tcp.crlf;
    l_body := l_body || 'Batch insert completed. Please review the following new rows:' || utl_tcp.crlf;
    l_body := l_body || '----------------------------------------' || utl_tcp.crlf;

    -- 遍历新增记录
    FOR rec IN (SELECT "ID" FROM "my_table" WHERE "ID" BETWEEN v_min_id AND v_max_id ORDER BY "ID") LOOP
      l_body := l_body || '- Row ID: ' || rec."ID" || utl_tcp.crlf;
    END LOOP;

    l_body := l_body || utl_tcp.crlf;
    l_body := l_body || 'Thank you' || utl_tcp.crlf;

    apex_mail.send(
        p_to   => l_recipients,
        p_from => 'noreply@mail.com',
        p_body => l_body,
        p_subj => 'Batch Insert: Review New Row IDs' ); 
  END IF;
END;

• 临时表方案通用性更强，不受ID生成方式限制，且天然支持会话隔离；
• 若需支持更新操作，只需将触发器事件改为AFTER UPDATE，并调整收集的数据（如记录修改前后的字段值）；
• 确保Oracle Apex的邮件服务器配置正常，否则apex_mail.send无法发送邮件。

内容的提问来源于stack exchange，提问作者JB999