在表单上创建行时出错

问题描述

在我在 APEX 上的其中一个页面中尝试创建新行后，出现以下错误:

After I try to create a new row in one of my pages on APEX, the following error appears:

ORA-02291:违反完整性约束(BD43015.SYS_C001194280) - 未找到父键 ORA-06512:在BD43015.INSERT_STUDENT"，第 4 行 ORA-04088:执行触发器 'BD43015_STUDENT5'ORA-04088 期间出错.:在SYS.WWV_DBMS_SQL"，第 549 行 ORA-06512:在APEX_040000.WWV_FLOW_DML"，第 1121 行 ORA-22816:带有 RETURNING 子句的功能不受支持错误 无法处理表 V_ALLSTUDENTS 的行.好的

注意:我正在尝试使用此触发器:

NOTE: I'm trying to use this trigger:

create or replace trigger insert_student
instead of insert on v_allstudents
for each row
begin
  insert into members values(:new.memberID, :new.name, :new.birth, :new.regist_date, :new.address, :new.sex, :new.phone);
  insert into students values(:new.memberID, :new.guardian, :new.rank, :new.plan_name, :new.plan_value);
end;
/

还有这个观点:

create or replace view v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value) AS
select members.memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value
from members, students
where members.memberID = students.memberID
order by members.name;

表成员:

drop table members cascade constraints;
create table members(
  memberID number(10) not null,
  name varchar2(30) not null,
  birth date not null,
  regist_date date not null,
  address varchar2(50) not null,
  sex char(1) not null CHECK (sex IN ('F', 'M')),
  phone number(9),
  primary key(memberID)
);

表学生:

drop table students cascade constraints;
create table students(
  memberID number(10) not null,
  guardian varchar2(30),
  rank varchar2(20) not null,
  plan_name varchar2 (30) not null,
  plan_value number(10) not null,
  primary key(memberID),
  foreign key(memberID) references members(memberID)
);

编辑修复:我们有一个触发器与 memberID 的插入发生冲突.

EDIT FIX: We had a trigger in conflict with the insertion of the memberID.

推荐答案

我可以从 SQLPLUS 成功重现该问题.

I could successfully reproduce the issue from SQLPLUS.

以下语句有效.

declare
  v_memberid number;
begin
  insert into
  v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value)
  values
  (1,'Neo',to_date('13-Jan-2009','dd-mon-yyyy'),to_date('13-Jan-2009','dd-mon-yyyy'),'My address','M',1234,'Trinity',1,'My Plan',2525)
end;

以下语句不起作用并抛出 ORA-22816.

The following statement does not work and throws ORA-22816.

declare
  v_memberid number;
begin
  insert into
  v_allstudents(memberID, name, birth, regist_date, address, sex, phone, guardian, rank, plan_name, plan_value)
values
  (1,'Neo',to_date('13-Jan-2009','dd-mon-yyyy'),to_date('13-Jan-2009','dd-mon-yyyy'),'My address','M',1234,'Trinity',1,'My Plan',2525)
  returning memberID into v_memberid;
end;

这是 Oracle 文档中对错误的描述.

Here is the description for the error from Oracle documentation.

对象类型列、LONG 列、远程表、带有子查询的 INSERT、和 INSTEAD OF 触发器目前不支持 RETURNING 子句.

RETURNING clause is currently not supported for object type columns, LONG columns, remote tables, INSERT with subquery, and INSTEAD OF Triggers.

在这种情况下，要么 Oracle Apex 生成带有返回子句的 Insert 语句，要么您在 Oracle Apex 中编写了带有返回子句的定制代码.

In this case either Oracle Apex is generating an Insert statement with the returning clause or you have written bespoke code in Oracle Apex with a returning clause.

我建议使用 Oracle 顶点表单取消而不是触发器并在这两个表中插入值.

I would recommend doing away with instead of trigger and inserting values into both these tables using Oracle apex forms.

这篇关于在表单上创建行时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！