PL/SQL: SQL Statement ignored?

Question

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;

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

Anyone any guesses why?

thanks for the help

Solution

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;

which should be:

WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID;

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.

(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).

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.

Comments

[Richard Clare]

I understand the code that I'm running currently. Im just stumped with this error. Ive changed what you suggested but its still pulling the same error, I've checked all table and column names as well

[Richard Clare]

It seems to be around the ' nPlaced_order_count NUMBER;' line would its saying it does not exist. any ideas why? @AlexPoole

[Alex Poole]

The 'statement ignored' error doesn't tell you anything specific, you need to look at the error stack. If you can't see it in your client then you can query for it with select * from user_errors where name = 'CUSTOMER_AD' and type = 'TRIGGER'

[Richard Clare]

Thanks for that code extremely handy. It returned 'PL/SQL: ORA-00942: table or view does not exist' on line 7? totally stumped by this error

[Alex Poole]

@RichardClare - is your table called placed_order (as you said in the previous question) or placed_orders (as you have it in this one)?

[Joachim Isaksson]

@AlexPoole Most likely the problem since I set up an SQLfiddle to test, and with the correct table names and the :OLD change, I get no warnings or errors. sqlfiddle.com/#!4/8bd83/1

[Richard Clare]

@AlexPoole thank you i hate things like this with coding. its placed_order without the s. Things like this i can never spot, again thank you

[jpaugh]

@Alex From my perspective, the connection between "SQL statement Ignored" and select * from user_errors is the most important point here. Might want to emphasize/embellish that some.

[Alex Poole]

Thanks @jpaugh, I've added that to the answer. It's useful things like "procedure compiled with errors" messages too.