插入触发器 ORA-01422 后:fetch 返回的行数超过请求的行数

Question

谁能帮我完成下面的代码。有3张表：Customer_A1，Reservation_A1和Invoice_A1。我正在编写一个触发器，每次进行新预订时都会执行该触发器。

触发器将预先加载发票表中invoice_id (inv_id(、reservation_id (res_id(、客户名字 (cust_fname(、客户姓氏 (cust_lname( 和reservation_start_date的信息。

我的代码如下。创建触发器时没有编译错误。但是，当我在保留表中插入一个新行以使触发器执行时，它会通知我我的触发器有错误

ORA-01422：fetch 返回的行数超过请求的行数。

CREATE OR REPLACE TRIGGER invoice_after_reservation_made
AFTER INSERT
ON RESERVATION_A1
FOR EACH ROW
DECLARE
inv_id INVOICE_A1.INV_ID%type;
res_id INVOICE_A1.res_id%type;
room_id INVOICE_A1.room_id%type;
cust_fname INVOICE_A1.cust_fname%type;
cust_lname INVOICE_A1.cust_lname%type;
reservation_start_date INVOICE_A1.reservation_start_date%type;
cust_id RESERVATION_A1.cust_id%type;
BEGIN
--read reservation_id
res_id:= :new.res_id;
--read room_id
room_id:= :new.room_id;
--read reservation_start_date
reservation_start_date:= :new.reservation_start_date;
--read customer_id
cust_id:= :new.cust_id;
--create new invoice_id
SELECT MAX(INVOICE_A1.inv_id)+1 INTO inv_id FROM INVOICE_A1;
-- import value from CUSTOMER_A1 table to variable cust_fname, cust_lname
Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname INTO 
cust_fname,cust_lname 
FROM CUSTOMER_A1 
WHERE CUSTOMER_A1.cust_id=cust_id;
-- Insert record into invoice table
INSERT INTO INVOICE_A1
VALUES (inv_id,res_id,room_id,cust_fname,cust_lname,null,TO_DATE(TO_CHAR(reservation_start_date),'DD/MM/YYYY'),null);

END;

注意：我已经在互联网上查找了解决方案，但没有雪茄。人们说问题主要来自返回多行的 Select 语句。但是，我在上面代码中的选择查询仅返回一行。我还检查了表的数据，在 3 个表中没有违反实体和引用完整性 Customer_A1、Reservation_A1 和 Invoice_A1。 我什至将代码复制到单独的测试程序中，以便在读取输入后打印出所有变量。测试程序效果很好。我现在投降。 请帮我解决这个问题。我是新来的.谢谢

Answer 1

问题出在语句中

Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname INTO 
cust_fname,cust_lname 
FROM CUSTOMER_A1 
WHERE CUSTOMER_A1.cust_id=cust_id;

您可能的意思是"从CUSTOMER_A1查找数据，其中 CUSTOMER_A1.cust_id = 变量 'cust_id' 的值"。不幸的是，这不是它的解释方式。数据库将其读取为"从CUSTOMER_A1查找数据，其中 CUSTOMER_A1.cust_id = CUSTOMER_A1.cust_id" - 换句话说，它将每行的CUST_ID字段与自身进行比较，发现它们是相等的(NULL 值的情况除外(，并从该行返回数据。

编写PL/SQL时要记住的一个好规则是"永远不要为变量指定与您将要操作的列相同的名称"。考虑到这一点，您可以考虑将触发器重写为：

CREATE OR REPLACE TRIGGER invoice_after_reservation_made
AFTER INSERT
ON RESERVATION_A1
FOR EACH ROW
DECLARE
vInv_id INVOICE_A1.INV_ID%type;
vRes_id INVOICE_A1.res_id%type;
vRoom_id INVOICE_A1.room_id%type;
vCust_fname INVOICE_A1.cust_fname%type;
vCust_lname INVOICE_A1.cust_lname%type;
vReservation_start_date INVOICE_A1.reservation_start_date%type;
vCust_id RESERVATION_A1.cust_id%type;
BEGIN
--read reservation_id
vRes_id:= :new.res_id;
--read room_id
vRoom_id:= :new.room_id;
--read reservation_start_date
vReservation_start_date:= :new.reservation_start_date;
--read customer_id
vCust_id:= :new.cust_id;
--create new invoice_id
SELECT MAX(INVOICE_A1.inv_id)+1 INTO vInv_id FROM INVOICE_A1;
-- import value from CUSTOMER_A1 table to variable cust_fname, cust_lname
Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname
INTO vCust_fname, vCust_lname 
FROM CUSTOMER_A1 
WHERE CUSTOMER_A1.cust_id=cust_id;
-- Insert record into invoice table
INSERT INTO INVOICE_A1
VALUES (vInv_id, vRes_id, vRoom_id, vCust_fname, vCust_lname, null, 
TO_DATE(TO_CHAR(reservation_start_date),'DD/MM/YYYY'), null);
END invoice_after_reservation_made;