mercredi 6 mai 2015

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

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

Someone a solution?

Aucun commentaire:

Enregistrer un commentaire