如何在不满足条件的情况下阻止oracle触发器执行

Question

我已经根据审批级别创建了3个触发器。表->p_it_people、p_it_issues、p_it_Departments1个部门有2个审批人->p_it_people.approver='审批人1'和p_it_poeple.approval_level='审批人2'另外p_it_departments.approval_status=2(对于2个审批人(

第一个触发器发送电子邮件至p_it_people.approver='批准者1'，通过设置p_it_issues.approve_This=Y1''来批准问题然后Approver 1登录到应用程序n并设置approve_this='Y1'

第二个触发器触发，当approve_this='Y1'(前提条件(时，设置p_it_issues.approved=1并发送电子邮件至p_it_people.Prover='批准者2'现在，当Approver 2登录并设置approve_this='Y2'时——这应该会触发第三个触发器。然而，它在应用程序页面上未抛出任何发现异常的数据。没有Approver 3，但如果没有找到Approver 3并且***只更新p_it_issues.approved=2，我也不希望触发器执行。

附言：这种方法是非常静态的，我正在寻找动态的解决方案，但目前这需要硬编码审批级别。

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;

Answer 1

如果我理解正确，并且它是引发no_data_found的第三个触发器，则处理它；请参阅触发器底部的exception部分。

(...)
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;