Oracle trigger unable to include logic in if clause

Question

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

I can see a number of issues here; are you getting a specific error back from Oracle when you try to compile or execute the trigger? — pmdba
– pmdba, Commented May 26, 2020 at 10:51
Yes this will throw error. I need help with building this logically. I am not sure how should I write the if condition for this to function correctly . — Velocity
– Velocity, Commented May 26, 2020 at 11:58
1. PLS-00049: bad bind variable 'APP_USER' 2. PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare else elsif end exit for goto if loop mod null pragma raise return select update while wit — Velocity
– Velocity, Commented May 26, 2020 at 13:41

pmdba · Accepted Answer · 2020-05-27 12:05:41Z

1

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;

edited May 27, 2020 at 12:05

answered May 26, 2020 at 14:42

pmdba

7,0832 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Velocity Over a year ago

Thanks, this will certainly work for the second part of requirement where email needs to be sent to Approver 2. But in the IF clause, how will i put the condition-> when all APP_USER who are Approver 1, set approve_this='Y' then the cursor would open and execute? This is where i am getting stuck.

Collectives™ on Stack Overflow

Oracle trigger unable to include logic in if clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related