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.

CAn you Please Help Me

2 Answers 2

0

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.

2
0

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 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 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 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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