mysql - 我需要构建一个触发器，当已经为包机分配飞行员时，将飞行员分配为副飞行员

Question

DROP TRIGGER IF EXISTS COPILOT_ASSIGNED;

DELIMITER //
CREATE TRIGGER COPILOT_ASSIGNED 
    BEFORE INSERT ON crew 
    FOR EACH ROW
BEGIN
    IF CHAR_TRIP EXISTS AND CREW_JOB = 'Pilot'
    THEN SET NEW.CREW_JOB = 'Copilot';
    END IF;
END //
DELIMITER ; 

INSERT INTO crew VALUES (10019, 109, 'Pilot');
INSERT INTO crew VALUES (10019, 106, 'Pilot');

SELECT * FROM crew WHERE CHAR_TRIP = 10019;

我无法确定如何编写此触发器。我需要检查是否已为包机(CREW_TRIP)分配了飞行员(CREW_JOB)。如果是，则需要将飞行员更改为副驾驶(CREW_JOB)。

Answer 1

DROP TRIGGER IF EXISTS COPILOT_ASSIGNED;

DELIMITER //
CREATE TRIGGER COPILOT_ASSIGNED 
    BEFORE INSERT ON crew 
    FOR EACH ROW
BEGIN
    IF EXISTS (SELECT * FROM crew WHERE CHAR_TRIP = NEW.CHAR_TRIP AND 
CREW_JOB='Pilot')
    THEN SET NEW.CREW_JOB = 'Copilot';
    END IF;
END //
DELIMITER ; 

INSERT INTO crew VALUES (10019, 109, 'Pilot');
INSERT INTO crew VALUES (10019, 106, 'Pilot');