0

I'm having a lot of doubts about creating a Trigger After Insert that after entering a new flight checks if it is the first flight of the assigned plane.

In this case, update the Aircraft.description field, with the phrase “Inaugural flight on [date of the entered flight]”.

The table In question is this:

CREATE TABLE Flight(
    flightNumber nchar(6) NOT NULL,
    airlineICAOId nchar(3) NOT NULL,
    departureDate date NOT NULL,
    departureTime time(0) NOT NULL,
    fromAirport nchar(4) NOT NULL,
    toAirport nchar(4) NOT NULL,
    [type] nchar(2) NOT NULL,
    registrationNumber nchar(10) NULL,
    id nchar(3) NOT NULL,
CONSTRAINT PK_Flight_flightNumber_ICAOId_departureDate_departureTime PRIMARY KEY (flightNumber, airlineICAOId, departureDate, departureTime),
CONSTRAINT FK_Flight_AirlineRoute_flightNumber_ICAOId FOREIGN KEY (flightNumber, airlineICAOId) REFERENCES AirlineRoute,
CONSTRAINT FK_Flight_Airport_fromAirport FOREIGN KEY (fromAirport) REFERENCES Airport (ICAOId),
CONSTRAINT FK_Flight_Airport_toAirport FOREIGN KEY (toAirport) REFERENCES Airport (ICAOId),
CONSTRAINT FK_Flight_Aircraft_registrationNumber FOREIGN KEY (registrationNumber) REFERENCES Aircraft,
CONSTRAINT FK_Flight_CrewGroup_id FOREIGN KEY (id) REFERENCES CrewGroup,
CONSTRAINT FK_Flight_Airline_ICAOId FOREIGN KEY (airlineICAOId) REFERENCES Airline(ICAOId),
CONSTRAINT FK_Flight_FlightType_type FOREIGN KEY ([type]) REFERENCES FlightType ([type])
)

2 Answers 2

1

This should be able to get you started, please see below.

CREATE TRIGGER trgFlight_Inaugural ON dbo.Flight
    AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    ; -- Prior statement must be terminated
    WITH CTE_InaguralFlight AS
        (
        SELECT I.registrationNumber
            , I.departureDate
            , A.[description]
        FROM INSERTED AS I
            INNER JOIN dbo.Aircraft AS A ON A.registrationNumber = I.registrationNumber
        WHERE NOT(A.[description] LIKE 'Inaugural flight on%')
        )
    UPDATE CTE_InaguralFlight
    SET [description] = 'Inaugural flight on ' + CONVERT(NVARCHAR(20), departureDate, 120);

END

My only concern is that the [Description] may contain other things? In which case this will need to be updated. But if possible, I would think the Aircraft table should have a field dedicated to the InauguralFlightDate if that's what you are trying to track.

0
-1

No need for a CTE. Simply check for existence against inserted.

CREATE TRIGGER trgFlight_Inaugural
ON dbo.Flight
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE  A
    SET     A.description = 'Inaugural flight on ' + CONVERT(NVARCHAR(20), A.departureDate, 120)
    FROM    dbo.Aircraft AS A
    WHERE   EXISTS (
                       SELECT   1
                       FROM     inserted AS i
                       WHERE    i.registrationNumber = A.registrationNumber
                   );
END;
3
  • I think that your solution will update the Aircraft table on every insert into the Flight table, which I don't think is the intention. You are also evaluating potentially all records in the Aircraft table and then removing those not in the INSERTED table, potentially working with unnecessary numbers of records. Jun 17, 2022 at 18:13
  • Thank you for your Support with both answer's I did sort of a Mix with the both of your's trigger and work thank you a lot Jun 17, 2022 at 18:23
  • The exists statement isolates records to only those in INSERTED. The query optimizer doesn't "load all values, then remove"; it selects relevant records only. I did miss the bit that checks for existing values of "inaugural flight" though. Good catch.
    – Mike Petri
    Jun 19, 2022 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.