有关SQL函数和触发器的帮助 [英] Help on SQL Function and Triggers

问题描述

您好，我正在使用PostgreSQL

我有一个名为FLIGHT的表，我编写了一个触发器和一个函数，该函数实际上在更新或在此表中插入记录后会触发一个调用过程的触发器，该过程将查询其他表并在此其他表FLIGHT_STAT中插入记录. />
在FLIGHT_STAT表中，我放置:-
fligth_no，aircraft_type_id，seat_class，qty

对于每个aircraft_type_id，我应该获得3个不同的行，即我所拥有的每种级别的座位(俱乐部，黄金，标准座位)都为1行.

问题是触发触发器时，我仅获得第一行.

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 ?

推荐答案

FLIGHT_STAT

FLIGHT_STAT

DECLARE 座位整数； DECLARE A VARCHAR ( 3 )； DECLARE S VARCHAR ( 10 )； 开始 选择 SEAT_INFO.QTY INTO 座位 FROM 飞机 加入 FLIGHT 打开 FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID 加入 SEAT_INFO 打开 AIRPLANE.AIRCRAFT_TYPE_ID = SEAT_INFO. TYPE_ID 位置 FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO; 选择 AIRPLANE.AIRCRAFT_TYPE_ID INTO A FROM 飞机 加入 FLIGHT 打开 FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID 位置 FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO; 选择 SEAT_INFO.CLASS_NAME INTO S FROM 飞机 加入 FLIGHT 打开 FLIGHT.AIRPLANE_ID = AIRPLANE.AIRPLANE_ID 加入 SEAT_INFO 打开 AIRPLANE.AIRCRAFT_TYPE_ID = SEAT_INFO. TYPE_ID 位置 FLIGHT.FLIGHT_NO = NEW.FLIGHT_NO; 插入 INTO FLIGHT_STAT(FLIGHT_NO，AIRCRAFT_TYPE_ID，CLASS_TYPE，AVAIL_SEATS) 值(NEW.FLIGHT_NO，A，S，SEATS)； 返回新功能； END ;

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

FLIGHT_STAT

这篇关于有关SQL函数和触发器的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！