Help With Creating a Trigger

All we need is an easy explanation of the problem, so here it is.

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])
)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 2

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply