CODEWITHSUNDEEP
oracle-databaseplsqltriggersoracle-apex
Velocity
Velocity

Reputation: 479

Oracle trigger unable to include logic in if clause

I need help with below trigger. It's probably poorly written but hopeful to get some help on correcting it logically and syntactically.

So if APP_USER who is approver 1 (could be more than 1) selects approve_This=Y, then mail should be sent to the app_users who are Approver 2.

I am confused as to how can i write this code logically correct.

CREATE OR REPLACE TRIGGER  ISSUE_NOTIFY
BEFORE 

begin


If upper(v_username)=upper(:APP_USER) and v_approver='Approver 1' and v_approve_This='Y'
THEN

--when above condition satisfied then set approve_This='N' and send email as below to Approver 2--

Upvotes: 0

Views: 84

Answers (1)

pmdba
pmdba

Reputation: 7033

There appear to be several redundancies in your query and "if" condition. Also, APP_USER is an APEX application object, not an actual bind value. To reference its value/content from within PL/SQL, use something like this:

begin    
   -- make sure this query only ever returns a single row
   select nvl(i.approve_this,'N') 
     into v_approve_this 
     from p_it_departments i,
          p_it_people p 
    where i.dept_id=p.assigned_dept
      and i.dept_id=:new.related_dept_id 
      and upper(p.username) = upper(NVL(v('APP_USER'),USER)) ;

   if v_approve_this='Y'
   then
      ...

The EXCEPTION clause can only occur within a BEGIN/END construct to isolate a transaction. I'm not sure there's a way to embed it in a nested query the way you are trying to do. Perhaps something like the following (no guarantees):

declare
   cursor c1 is ... [your query]
begin
   if ... 
   then
      open c1;
      loop
         fetch c1 into v_person_id,v_email,v_Dept_name;
         exit when c1%notfound;
         apex_mail.send (...);
      end loop;
      close c1;
   end if;
exception
   when no_data_found then 
   -- set APPROVED as N anyway
   :new.approve_this='N';
end;

Upvotes: 1

Related Questions