Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger to loop and send incremental emails based on condition

VinipandaMay 18 2020 — edited May 18 2020

I am trying to write a trigger as below. I need to further modify it so that it runs sequentially. i.e.

When an issue is raised in related dept, it passes through multiple approval levels.

So below code is w.r.t. to 1st approval level. I am trying to write code so that for first level, mail goes to all people with p.approver ='Approver 1' in related dept.

Then when approved ='Y' from all the people in application, i.e. both X and Y who are Approver 1 set Approved button as Y logging in separately,(it is via oracle apex app). Then email would shoot to next Approver 2 and so on. So could you please suggest ways i could make this trigger dynamic so that email is triggered from Approver 1...n for prticular dept.

CREATE OR REPLACE EDITIONABLE TRIGGER "P_IT_ISSUES_AIU_Notify_Approver_1" AFTER insert on P_IT_ISSUES for each row FOLLOWS P_IT_ISSUES_AIU_EMAIL begin For c1 in( select p.person_id ,p.person_email,i.dept_name from p_it_people p,p_it_departments i where p.assigned_dept=i.dept_id and i.dept_id=:new.related_dept_id and p.approver='Approver 1') --should become incremental i.e. Approver 1..n Loop APEX_MAIL.SEND( p_to => c1.email, p_from => c1.email, p_body => 'You have been assigned a new issue for first level approval. ' ||chr(10)|| ' The details are as follows ::: ' ||chr(10)|| chr(10)|| ' Department:'|| c1.dept_name ||chr(10)|| ' Summary: '||:new.issue_summary ||chr(10)|| ' Status: '||:new.status ||chr(10)|| ' Priority: '||nvl(:new.priority,'-'), p_subj => 'New Issue for nth Level Approval'); End loop; end;

In short: The trigger mentioned is when an issue is raised, it would go to person with approver='Approver 1', he is first approver. When he logs into application and sets p_it_issues.is_approved='Y', then email would shoot to p_it_people.Approver='Approver 2' . He would again log in, set p_it_issues.is_approved='Y' and email would shoot to p_it_people.Approver='Approver 3' . There could be more than one person with p_it_people.Approver='Approver 1' or 2 or 3. So all would need to approve it and set Y .

Looking forward to your help.

Comments

BEDE

Ouch! Do you realize that, provided you do write that trigger, an insert into that table will last very long? Better create a table mails2send that will have a foreign key to P_IT_ISSUES and have that trigger insert into mails2send  the value of the PK for that row in P_IT_ISSUES. And have a job scan mails2send  and send the emails. Otherwise, your transaction processing will be very slow, most likely inacceptable.

For sending e-mails from PL/SQL use utl_smtp or utl_mail.

Vinipanda

I know, but need to do this temporarily. Will use utl_smtp later on but for now this needs to be done.

Any idea how the trigger can be modified to loop?

Vinipanda

The trigger mentioned is when an issue is raised, it would go to person with approver='Approver 1', he is first approver. When he logs into application and sets p_it_issues.is_approved='Y', then email would shoot to p_it_people.Approver='Approver 2' . He would again log in, set p_it_issues.is_approved='Y' and email would shoot to p_it_people.Approver='Approver 3' . There could be more than one person with p_it_people.Approver='Approver 1' or 2 or 3. So all would need to approve it and set Y .

Billy Verreynne

A trigger like this sends e-mails BEFORE the transaction is committed - which means that e-mails can be send via UTL_SMTP, and the transaction rolled back. Thus users can receive e-mails for transactions never committed.

Simplistic solution:

Create a stored proc that can be called to send e-mail for a row PK parameter, e.g. SendMail( mailID ).

In the trigger call DBMS_JOB.submit() to execute the SendMail() proc for the relevant row.

Only when the transaction is committed, will the jobs scheduled queued for execution.

Note that when using APEX_MAIL from a trigger, workspace security ID needs to be set - and you need to ensure the e-mails are queued without being committed for sending.

2 people found this helpful
Mike Kutz

My Understanding

  • APEX_MAIL uses UTL_MAIL under the hood.
    • APEX_MAIL just makes it easier to send multiple attachments
    • AFAIK - APEX_MAIL now supports email Templates.
  • APEX_MAIL queues the email
    • ROLLBACK will undo the queuing
  • You need a running DBMS_SCHEDULER job to process the queue
    • DBA_SCHEDULER_JOBS where job_name='ORACLE_APEX_MAIL_QUEUE' and owner='APEX_nnnnnn'
  • Using APEX_MAIL.push_queue in a Trigger (or 2nd Process) is really bad.
    • Oracle can "rollback and try again"
      • You can get multiple identical emails sent
    • APEX can "rollback and try again"
      • You can get multiple identical emails sent
    • Rapidly sending multiple identical emails can get you Black Listed faster than you can hit the SEND button.

From my understanding, APEX_MAIL already does 90% of what Billy and BEDE are recommending.

The other 10% is about "how you interact with it".

My recommendation to APEX developers

  • put your "send email" code in a dedicated package
  • Call the "Send Email" code as part of a 2nd Process, not within a trigger.
  • If you need a non-APEX apps to use the same "Send Email" code, force that application to use an API (Package)
    • REVOKE insert, update, delete ON table_in_question FROM application_users_and_roles
    • Use a Table API (TAPI) or Transaction API (XAPI) [a package]
    • The TAPI/XAPI should be responsible for setting up the APEX Session if necessary. (see APEX_MAIL documentation for HOWTO setup APEX Session)

In short

  • Keep your DML code separate from your "Send Email" code.
  • Only send emails on committed data. (use a queue so that your can "send email later after commit")

My $0.02

MK

PS - this is mostly for OP, not Billy/BEDE

1 person found this helpful
Billy Verreynne

Mike, it uses UTL_SMTP - UTL_MAIL is far too primitive.

1 - 6

Post Details

Added on May 18 2020
#apex, #oracle, #plsql, #trigger
6 comments
882 views