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