Help With Creating a Trigger

Question

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.

CAn you Please Help Me

Answer 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;

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

Answer 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;