Forum Stats

  • 3,874,178 Users
  • 2,266,677 Discussions
  • 7,911,757 Comments

Discussions

Trigger to loop and send incremental emails based on condition

Vinipanda
Vinipanda Member Posts: 108 Red Ribbon
edited May 18, 2020 8:42AM in SQL & PL/SQL

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.

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">CREATE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">OR</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> REPLACE EDITIONABLE </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">TRIGGER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">  </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">"P_IT_ISSUES_AIU_Notify_Approver_1"</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/>AFTER </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">insert</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">on</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> P_IT_ISSUES <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">for</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> each </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">row</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/>FOLLOWS P_IT_ISSUES_AIU_EMAIL <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">begin</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">For</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> c1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">in</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">person_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">p</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">person_email</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">i</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">dept_name  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p_it_people p</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">p_it_departments i <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">assigned_dept</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">i</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">dept_id </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> i</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">dept_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">related_dept_id </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">approver</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">'Approver 1'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="com" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-400);">--should become incremental i.e. Approver 1..n</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/>Loop  APEX_MAIL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">SEND</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p_to </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> c1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">email</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p_from </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> c1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">email</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/>p_body </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">'You have been assigned a new issue for first level approval.  '</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||<br/></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">' The details are as follows ::: '</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">' Department:'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> c1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">dept_name </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">' Summary: '<br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">issue_summary </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">' Status: '</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">status </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">chr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">10</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">' Priority: '</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">nvl</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(:</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">priority</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">'-'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> p_subj </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">=></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--red-800);">'New Issue for nth Level Approval'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">End</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> loop</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);">end</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">;</span>

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.
BEDENextName

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy
    edited May 18, 2020 1:34AM

    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
    Vinipanda Member Posts: 108 Red Ribbon
    edited May 18, 2020 2:06AM

    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
    Vinipanda Member Posts: 108 Red Ribbon
    edited May 18, 2020 4:22AM

    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
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited May 18, 2020 4:23AM

    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.

    BEDENextName
  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown
    edited May 18, 2020 8:39AM

    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

    BEDE
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited May 18, 2020 8:42AM

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