oracle-databaseif-statementtriggerscompiler-errorsora-00936

SQL IF ELSE statement in trigger


I want to trigger every insert on a table. Sometimes the name_event is the repository of the target table available and can this be inserted. Sometimes it's unavailable and than I need to check the code in the folder table:

create or replace TRIGGER scheme2.Logger
  BEFORE INSERT OR UPDATE ON scheme2.payments
  FOR EACH ROW
BEGIN
    INSERT INTO scheme1.db(ID, NAME_EVENT, TIME_EVENT)
    VALUES(  :NEW.id 
          ,  IF (select repo.code 
                   from scheme2.repository repo
                  where repo.id = :NEW.repository_id) IS NOT NULL
             THEN 
                (select repo.code
                   from scheme2.pay_repository repo
                  where repo.id = :NEW.repository_id)
             ELSE
                (select env.repository_code 
                   from scheme2.folder env
                  where env.id = :NEW.folder_id)
          , SYSDATE);
END;

This code I tried, but I got compile errors:

Error(2,1): PL/SQL: SQL Statement ignored

Line 2 is the line for each row

Error(4,5): PL/SQL: ORA-00936: Unknown expression.

Line 4 is the insert into line

How to fix the errors?


Solution

  • create or replace TRIGGER scheme2.Logger BEFORE INSERT OR UPDATE ON scheme2.payments
        FOR EACH ROW
       declare
       VAR_NEVENT  number(3);
    
        BEGIN
    
       select repo.code into VAR_NEVENT from scheme2.repository repo
       where repo.id = :NEW.repository_id)
    
         IF VAR_NEVENT  IS NOT NULL THEN 
        select repo.code INTO VAR_NEVENT from scheme2.pay_repository repo where repo.id =  :NEW.repository_id
    
     ELSE (select env.repository_code into VAR_NEVENT  from scheme2.folder env where env.id = :NEW.folder_id)
        END IF;
    
        INSERT INTO scheme1.db(ID, NAME_EVENT, TIME_EVENT)
        VALUES(:NEW.id, VAR_NEVENT  , SYSDATE);
        END;
    

    Something similar to the above .. first select your result in a variable then insert it in a table.