创建触发器时出错-PL/SQL:ORA-01747:无效的user.table.column、table.column或

Question

我正在创建一个触发器，希望在其中更新另一个表中的字段。请参阅下面我写的触发器：

create or replace
 TRIGGER RE_SE_INSERT
 AFTER INSERT OR UPDATE ON USER FOR EACH ROW
 BEGIN
 update user set 
 :new.ci_No = (select ci_No from customer where ci_id = :new.ci_id),
 :new.ci_name = (select ci_name from customer where ci_id = :new.ci_id),
 :new.IS_deleted = (select deleted from customer where ci_id = :new.ci_id);
 END;

我已经检查了列名是否错误，但根据表，所有列名都是正确的，但我仍然得到ORA-01747 error of invalid table.column。

你能帮我一下吗？

提前感谢！Tapan

Answer 1

 create or replace trigger RE_SE_INSERT
 BEFORE INSERT OR UPDATE  --create a BEFORE insert/update trigger
 ON USER FOR EACH ROW
 DECLARE
  CURSOR new_cur
  IS 
    SELECT ci_no
          ,ci_name
          ,deleted
     FROM customer
     where ci_id = :new.ci_id;
   new_row  new_cur%ROWTYPE;
 BEGIN
  --update user set   --remove this statement
  OPEN new_cur;
  FETCH new_cur INTO new_row;
   IF new_cur%FOUND THEN
    :new.ci_No      := new_row.ci_no;
    :new.ci_name    := new_row.ci_name;
    :new.IS_deleted := new_row.deleted;
   END IF;
  CLOSE new_cur;
END RE_SE_INSERT;
/