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.

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.