Plpgsql ERROR: missing FROM-clause entry for table when trying to insert

1
votes

Every time I try to insert something in table SaleosOrderDetail I get the following error:

ERROR: missing FROM-clause entry for table "salesorderdetail" LINE 1: SELECT CustomerID FROM SalesOrderHeader WHERE SalesOrderDeta... QUERY: SELECT CustomerID FROM SalesOrderHeader WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID CONTEXT: PL/pgSQL function new_order_detail() line 6 at SQL statement

********** Error **********

ERROR: missing FROM-clause entry for table "salesorderdetail" SQL state: 42P01 Context: PL/pgSQL function new_order_detail() line 6 at SQL statement

The code is:

CREATE OR REPLACE FUNCTION new_order_detail()
RETURNS trigger AS
$BODY$
DECLARE CustID INT;
BEGIN


    SELECT CustomerID FROM SalesOrderHeader WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID INTO custID;
    UPDATE Customer SET number_of_items = number_of_items + 1 WHERE CustomerID = custID ;



END;
$BODY$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS new_order ON SalesOrderDetail;

CREATE TRIGGER new_order
    AFTER INSERT OR UPDATE ON SalesOrderDetail
    FOR EACH ROW EXECUTE PROCEDURE new_order_detail();
2
postgresqlplpgsql
As the error says, the error is in the first SELECT statement in the trigger function. You're trying to use a table (SalesOrderDetail) that you are not selecting from. - Patrick White

2 Answers

1
votes

try replace :

   SELECT CustomerID FROM SalesOrderHeader WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID INTO custID;

by : 

SELECT INTO custID CustomerID FROM SalesOrderHeader WHERE SalesOrderHeader.SalesOrderID=NEW.SalesOrderID;
1
votes

You are missing table SalesOrderDetail. Probably you want:

SELECT CustomerID 
    FROM SalesOrderHeader, SalesOrderDetail
   WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
INTO custID;

This form of JOIN is obsolete. Use modern form instead:

SELECT CustomerID 
    FROM SalesOrderHeader
         JOIN SalesOrderDetail
         ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
INTO custID;

Because PostgreSQL SQL is case insensitive, then camel notation is not recommended. Use underscore character instead:

Wrong: CustomerID, Good: custmer_id