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