视图上的instead of触发器函数

wxclj1h5 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(265)

我来到了另一个停止，这是代替触发器，应该能够在视图上的功能。

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

我得到的错误（但我不知道如何解决它们）是：pls-00049:无效的绑定变量'new.hospital\u id'（依此类推所有其他'new.xyz'条目）
pls-00103：找不到“插入”符号。
我就是找不到解决的办法，不管我怎么努力：/提前谢谢大家！

sql oracle plsql

来源：https://stackoverflow.com/questions/61975026/instead-of-trigger-function-on-view

1条答案

按热度按时间

o0lyfsai1#

正如@gordonlinoff在他的评论中指出的，您不能在instead-of触发器中使用视图无法获取的字段。因此，您必须更改视图以获取所需的字段：

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

然后你的扳机就会变成

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<>在这里摆弄

赞(0）回复(0）举报 2021-07-26

我来回答

视图上的instead of触发器函数

1条答案

相关问题

热门标签

最新问答