CODEWITHSUNDEEP
sqlpostgresql
user13413898
user13413898

Reputation:

query has no destination for result data (RETURNS TRIGGER)

I have got an issue where on insert I want to update instead if the customerID exists in a separate table called leadCustomer. I'm receiving the error in the title and honestly have no idea why, if anyone could give any advice I would really appreciate it.

CREATE TRIGGER tr_Ins_FlightBooking
BEFORE INSERT ON FlightBooking
FOR EACH ROW EXECUTE PROCEDURE update_Lead();

CREATE OR REPLACE FUNCTION update_Lead()
RETURNS TRIGGER AS 
$BODY$
    BEGIN
      SELECT * FROM FlightBooking, LeadCustomer;
      IF FlightBooking.CustomerID = LeadCustomer.CustomerID
      THEN UPDATE FlightBooking AS fb
      SET NumSeats = OLD + NEW, TotalCost = OLD + NEW
      FROM fb JOIN LeadCustomer AS lc ON
      fb.CustomerID = lc.CustomerID
      WHERE fb.CustomerID != lc.CustomerID;
      END IF;
    END;
$BODY$ 
LANGUAGE plpgsql;

Many thanks, Callum

Upvotes: 0

Views: 107

Answers (1)

IMSoP
IMSoP

Reputation: 97858

There seem to be several quite fundamental errors here:

  • As the error says, there is nowhere for the results of your SELECT statement to go.
  • On the next line, you access FlightBooking and LeadCustomer as though they are records or rows; presumably you expect these to come from the SELECT, but which row would they contain?
  • Later, you write OLD + NEW, but OLD and NEW are records - they represent the whole row the trigger is being run for, not any particular column.
  • This is an INSERT trigger, so there are no OLD values, only the NEW ones being inserted.
  • You JOIN LeadCustomer on the CustomerID column, but that row won't have been inserted yet, because this is a BEFORE trigger.
  • You also have a WHERE clause that contradicts your ON clause, so no rows can possibly match (ON fb.CustomerID = lc.CustomerID WHERE fb.CustomerID != lc.CustomerID)
  • You don't RETURN anything to indicate whether the original INSERT should go ahead or not.

If you look at the Postgres manual for triggers, you'll see a few examples, and I don't want to write your code for you because I think it's better to learn by trying, but here are some hints:

  • Your main condition is if there is a row matching the inserted CustomerID (which will be in the variable NEW.CustomerID) in the table LeadCustomer; you could use an IF EXISTS (...) query to see if there is a row matching this condition.
  • In your update, you want to add the values from NEW (the inserted row) to an existing row in the table, not to OLD.
  • I don't think you need the JOIN in that update, just to match WHERE CustomerID = NEW.CustomerID, but I'm not entirely clear what you're trying to do.
  • If you run the update, you want to skip the insert so RETURN NULL; if you don't run the update, you need to tell Postgres to go ahead with the insert, so RETURN NEW (this is explained in the manual).

Upvotes: 1

Related Questions