Help on SQL Function and Triggers

See more:

SQL

Hello, I am using PostgreSQL

I have a table named FLIGHT, I have wrote a trigger and a function that practically after updating or inserting records in this table it fires a trigger that calls a procedure, that will Query other tables and insert records in this other table FLIGHT_STAT.

In the FLIGHT_STAT table I am placing :-
fligth_no, aircraft_type_id, seat_class, qty

Where for each aircraft_type_id I should get 3 different rows, that is 1 row for every class of seats that I have which are club,gold,standard.

The problem is that I am getting only the first row when firing the trigger.

CREATE FUNCTION FLIGHT_STAT() RETURNS TRIGGER AS $FLIGHT_STAT$

DECLARE SEATS INTEGER; 
DECLARE A VARCHAR(3) ;
DECLARE S VARCHAR(10) ;

	
BEGIN
 
	SELECT SEAT_INFO.QTY INTO SEATS
	FROM AIRPLANE 
	JOIN FLIGHT ON FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID 
	JOIN SEAT_INFO ON AIRPLANE.AIRCRAFT_TYPE_ID = SEAT_INFO.TYPE_ID
	WHERE FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO;
  	
	SELECT AIRPLANE.AIRCRAFT_TYPE_ID INTO A
	FROM AIRPLANE
	JOIN FLIGHT ON FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID
	WHERE FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO;
	
	SELECT SEAT_INFO.CLASS_NAME INTO S
	FROM AIRPLANE 
	JOIN FLIGHT ON FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID 
	JOIN SEAT_INFO ON AIRPLANE.AIRCRAFT_TYPE_ID = SEAT_INFO.TYPE_ID
	WHERE FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO;
		
	
	INSERT INTO FLIGHT_STAT(FLIGHT_NO,AIRCRAFT_TYPE_ID,CLASS_TYPE,AVAIL_SEATS)
	VALUES (NEW.FLIGHT_NO, A ,S, SEATS);	


 RETURN NEW;
END;
$FLIGHT_STAT$LANGUAGE plpgsql;

CREATE TRIGGER AVAIL_S AFTER INSERT OR UPDATE ON FLIGHT
    FOR EACH ROW EXECUTE PROCEDURE FLIGHT_STAT();

Any help please on how can I modify the function to get all the three rows in return ?