INSTEAD OF TRIGGER function on VIEW

Question

I came to another stop which is the INSTEAD OF TRIGGER which should be able to function on the VIEW.

CREATE OR REPLACE VIEW hospital_specialty AS SELECT street, housenumber,
postcode, place,designation, contactperson FROM hospital, specialization WHERE hospital_id, hospital;


CREATE OR REPLACE TRIGGER hospital_trigger
INSTEAD OF INSERT ON hospital_specialty
FOR EACH ROW
DECLARE 
v_hospitalid INT;
v_street  VARCHAR(45);
v_hnumber INT;
v_postcode INT;
v_place VARCHAR(45);
v_reachability VARCHAR(45);



BEGIN
v_hospitalid := :NEW.hospital;
v_street := :NEW.street;
v_hnumber  := :NEW.housenumber;
v_postcode := :NEW.postcode;
v_place := :NEW.place;
v_reachability := :NEW.reachibility_phone;


INSERT INTO hospital VALUES (v_hospitalid , v_street , v_hnumber, v_postcode , v_place, v_reachability);

INSERT INTO specialty VALUES (:NEW.specialty_ID, :NEW.designation, :NEW.contactperson, :NEW.hospital);
END;
/

The errors I get (but I do not know how to solve them) are: PLS-00049: Invalid Bind-Variable 'NEW.hospital_ID' (and so on for all the other 'NEW.xyz' entries)

and PLS-00103: Can't find Symbol "INSERT".

I just can't get a solution, no matter what i try :/ Thanks everyone in advance!

Answer 1

As noted by @GordonLinoff in his comment, you can't use a field in the INSTEAD OF trigger which the view doesn't fetch. As such, you'll have to change the view to fetch the fields you need:

CREATE OR REPLACE VIEW HOSPITAL_SPECIALTY AS
  SELECT h.HOSPITALID,    -- HOSPITAL fields
         h.STREET,
         h.HOUSENUMBER,
         h.POSTCODE,
         h.PLACE,
         h.REACHABILITY,
         s.SPECIALTY_ID,  -- SPECIALIZATION fields
         s.DESIGNATION,
         s.CONTACTPERSON,
         s.HOSPITAL   
  FROM HOSPITAL h
  INNER JOIN SPECIALIZATION s
    ON h.HOSPITALID = s.HOSPITAL

Then your trigger becomes

CREATE OR REPLACE TRIGGER HOSPITAL_SPECIALTY_II
  INSTEAD OF INSERT ON HOSPITAL_SPECIALTY
  FOR EACH ROW
BEGIN
  INSERT INTO HOSPITAL
    (HOSPITAL_ID,       STREET,      HOUSENUMBER,
     POSTCODE,          PLACE,       REACHABILITY)
  VALUES
    (:NEW.HOSPITAL_ID, :NEW.STREET, :NEW.HOUSENUMBER,
     :NEW.POSTCODE,    :NEW.PLACE,  :NEW.REACHABILITY);

    INSERT INTO SPECIALIZATION
      (SPECIALTY_ID,       DESIGNATION,     CONTACTPERSON,      HOSPITAL)
    VALUES
      (:NEW.SPECIALTY_ID, :NEW.DESIGNATION, :NEW.CONTACTPERSON, :NEW.HOSPITAL);
END HOSPITAL_SPECIALTY_II;

db<>fiddle here