CODEWITHSUNDEEP
oracle-databaseoracle-apex
Velocity
Velocity

Reputation: 479

How to stop oracle trigger from executing despite condition not satisfied

I have created 3 triggers based on approval levels.Tables-> p_it_people,p_it_issues,p_it_Departments There are 2 Approvers for 1 department -> p_it_people.approver='Approver 1' and p_it_people.approval_level='Approver 2' Also p_it_departments.approval_status=2 (for 2 approvers)

1st trigger sends email to p_it_people.approver='Approver 1' to approve the issue by setting p_it_issues.approve_This=Y1'' Then Approver 1 logs in to the applicationn and sets approve_this='Y1'

2nd trigger fires , when approve_this='Y1' (pre-condition) ,set p_it_issues.approved=1 and send email to p_it_people.approver='Approver 2' Now when Approver 2 logs in and sets approve_this='Y2'--This should fire 3rd trigger. However it throw no data found exception on application page. There is no Approver 3, but i also dont want the trigger to execute at all if it doesn't find Approver 3 and ***just update p_it_issues.approved=2.

P.S. The approach is very static and i am looking for dynamic solution but for now this needs to be done hard coding approval levels.

Code for Trigger 3:

    CREATE OR REPLACE EDITIONABLE TRIGGER  "P_IT_ISSUES_AIU_Notify_Approver_3" 
    BEFORE 
    update on P_IT_ISSUES
    for each row 
         WHEN (new.APPROVE_THIS ='Y2') declare
    v_person_id number;
    v_email varchar2(255);
    v_dept_name varchar2(50);

    begin
    :new.APPROVED :=2 ;


    select p.person_id ,p.person_email,i.dept_name into v_person_id,v_email,v_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 3' and i.approval_level!=:new.approved ;

                 APEX_MAIL.SEND( 
                     p_to => v_email, 
                     p_from => v_email, 
                     p_body =>  
                     'You have been assigned a new issue for third level approval.  ' ||chr(10)|| 
                     'The details are below. ' ||chr(10)|| 
                     chr(10)|| 
                     ' Department:'|| v_dept_name ||chr(10)|| 
                     ' Summary: '||:new.issue_summary ||chr(10)|| 
                     ' Status: '||:new.status ||chr(10)|| 
                     'Priority: '||nvl(:new.priority,'-'), 
                      p_subj => 'New Issue for Third Level Approval'); 


    end;

In case required, codes for Trigger 1 and 2 are also below:

Trigger 1:

    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
    declare
    v_person_id number;
    v_email varchar2(255);
    v_dept_name varchar2(50);
    begin
    select p.person_id ,p.person_email,i.dept_name into v_person_id,v_email,v_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' and i.approval_level!=:new.approved ;

                 APEX_MAIL.SEND( 
                     p_to => v_email, 
                     p_from => v_email, 
                     p_body =>  
                     'You have been assigned a new issue for first level approval.  ' ||chr(10)|| 
                     'The details are below. ' ||chr(10)|| 
                     chr(10)|| 
                     ' Department:'|| v_dept_name ||chr(10)|| 
                     ' Summary: '||:new.issue_summary ||chr(10)|| 
                     ' Status: '||:new.status ||chr(10)|| 
                     'Priority: '||nvl(:new.priority,'-'), 
                      p_subj => 'New Issue for First Level Approval'); 


    end;

Trigger 2:

CREATE OR REPLACE EDITIONABLE TRIGGER  "P_IT_ISSUES_AIU_Notify_Approver_2" 
BEFORE 
update on P_IT_ISSUES
for each row 
   WHEN (new.APPROVE_THIS ='Y1'  ) declare
v_person_id number;
v_email varchar2(255);
v_dept_name varchar2(50);

begin
:new.APPROVED :=1 ;


select p.person_id ,p.person_email,i.dept_name into v_person_id,v_email,v_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 2'  and i.approval_level!=:new.approved;

             APEX_MAIL.SEND( 
                 p_to => v_email, 
                 p_from => v_email, 
                 p_body =>  
                 'You have been assigned a new issue for second level approval.  ' ||chr(10)|| 
                 'The details are below. ' ||chr(10)|| 
                 chr(10)|| 
                 ' Department:'|| v_dept_name ||chr(10)|| 
                 ' Summary: '||:new.issue_summary ||chr(10)|| 
                 ' Status: '||:new.status ||chr(10)|| 
                 'Priority: '||nvl(:new.priority,'-'), 
                  p_subj => 'New Issue for Second Level Approval'); 


end;

Upvotes: 0

Views: 47

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

If I understood you correctly, and if it is the 3rd trigger that raises no_data_found, then handle it; see the exception section at the bottom of the trigger.

(...)
begin
  :new.APPROVED :=2;

  select p.person_id, p.person_email, i.dept_name 
    into v_person_id, v_email, v_Dept_name 
  from p_it_people p join p_it_departments i on p.assigned_dept = i.dept_id
  where i.dept_id = :new.related_dept_id 
    and p.approver = 'Approver 3' 
    and i.approval_level != :new.approved;

  APEX_MAIL.SEND(...);

exception
  when no_data_found then 
    -- set APPROVED anyway
    :new.APPROVED :=2;
end;

Upvotes: 1

Related Questions