PL/SQL:忽略了SQL语句? [英] PL/SQL: SQL Statement ignored?

问题描述

大家好，尝试创建触发器时会收到此错误消息，这让我有些困惑. 这是我的触发代码.

Hi everyone getting this error message when trying to create a trigger and its got me a little stumped. Here is my trigger code.

CREATE OR REPLACE TRIGGER CUSTOMER_AD
  AFTER DELETE ON CUSTOMER
  REFERENCING OLD AS OLD
  FOR EACH ROW
DECLARE
  nPlaced_order_count  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nPlaced_order_count
    FROM PLACED_ORDERS p
    WHERE p.FK1_CUSTOMER_ID = OLD.CUSTOMER_ID;
IF nPlaced_order_count > 0 THEN
INSERT into previous_customer
(customer_id,
first_name,
last_name,
address,
AUDIT_USER,
AUDIT_DATE)
VALUES
(:old.customer_id,
:old.first_name,
:old.last_name,
:old.address,
UPPER(v('APP_USER')),
SYSDATE);
END IF;
END CUSTOMER_AD;

我得到的错误是第4行错误:PL/SQL:SQL语句被忽略0.10秒"

And the error I'm getting 'Error at line 4: PL/SQL: SQL Statement ignored 0.10 seconds'

有人猜为什么吗?

感谢您的帮助

推荐答案

显示的错误只是最高级别.根据运行的位置，您应该能够看到堆栈跟踪.客户将确切地确定该如何做；无论如何，SQL * Plus或SQL Developer会向您显示更多信息，但是我对其他客户端并不十分了解.如果您无法在客户中看到详细信息，则可以使用以下方法查询它们:

The error shown is only the highest level. Depending where you're running it you should be able to see the stack trace. The client will determine exactly how to do that; SQL*Plus or SQL Developer would show you more than this anyway, but I don't really know about other clients. If you can't see the details in your client then you can query for them with:

select * from user_errors where name = 'CUSTOMER_AD' and type = 'TRIGGER'

假设所有表都存在，可能是这一行:

Assuming the tables all exist, it's probably this line:

WHERE p.FK1_CUSTOMER_ID = OLD.CUSTOMER_ID;

应为:

WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID;

当引用表中的旧(或新)值时，在referencing子句中指定的名称前面带有冒号，因此在这种情况下为:OLD.正如您在insert ... values()子句中所做的那样.

When referencing the old (or new) value from the table, the name as specified in the referencing clause has be preceded by a colon, so :OLD in this case. As you're doing already in the insert ... values() clause.

(根据评论，我的假设被证明是错误的-以及遗漏的冒号问题，表名实际上是placed_order，而没有s).

(From comments, my assumption turned out to be wrong - as well as the missing colon problem, the table name is really placed_order, without an s).

好像您从上一个问题的两个答案中复制了代码，但并没有真正了解他们在做什么.您可能需要查看触发器设计准则(尤其是关于不复制数据库功能的内容)和 create trigger 的语法，其中引入了referencing子句.

Seems like you copied code from both answers to your previous question without really understanding what they were doing. You might want to look at the trigger design guidelines (particularly the one about not dupicating database functionality) and the syntax for create trigger which introduces the referencing clause.

这篇关于PL/SQL:忽略了SQL语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！