Triggers

Start Omhoog Stored Routines Triggers Scheduled Events Performance Import and Export Data Storage Engines Transactions Server Administration Users and Access Control Oefeningen Database Schema's

Database automatisatie

  • Triggers en scheduled events hebben de neiging om samen te gaan met opgeslagen procedures.
  • Triggers en scheduled events maken het mogelijk dat één of meer SQL-statements automatisch worden uitgevoerd door de server.
  • Dit voorziet in een krachtig kader voor het automatiseren van database operaties.
  • Inzicht Triggers

    Een trigger, zoals de naam suggereert, verwijst naar één of meer SQL statements die automatisch uitgevoerd ("getriggerd") worden door de databaseserver wanneer een bepaalde gebeurtenis plaatsvindt. Triggers kunnen van pas komen bij het automatiseren van database operaties, en daarmee een deel van de belasting door een toepassing te reduceren.

    Bekende voorbeelden van triggers gebruikt omvatten:

  • Loggen van veranderingen in data
  • Creëren van "snapshots" van gegevens voorafgaand aan een verandering (voor undo functionaliteit)
  • Uitvoeren van automatische berekeningen
  • Veranderen van gegevens in een tabel in reactie op een verandering in een andere tabel
  • Een trigger wordt altijd met een bepaalde tabel geassocieerd , en kan worden ingesteld om uitgevoerd te worden voordat of nadat de trigger-gebeurtenis plaatsvindt. MySQL ondersteunt op dit moment drie soorten trigger events: inserts, updates en verwijdert.

    Triggertype en triggermodus

    Om een trigger te definiëren, biedt MSSQL/MySQL de CREATE TRIGGER commando. Dit commando wordt gevolgd door de triggernaam en de vier trigger componenten, namelijk:

  • Trigger event: INSERT, UPDATE of DELETE
  • Trigger activering tijd: na de gebeurtenis of voordat
  • Trigger onderwerp tabel: de tabel waarin het trigger evenement plaatsvindt
  • Trigger body: bevat de uit te voeren SQL-statements
  • Opmerking: Om een trigger aan te maken, moet in MySQL de gebruiker het TRIGGER privilege (in MySQL 5.1.6+) of de SUPER privilege (in MySQL 5.0.x) te hebben.

    Een eenvoudige Trigger

    loggen van wijzigingen in een databank van een luchtvaartmaatschappij

    Laten we veronderstellen dat elke keer dat een gebruiker een nieuwe vlucht aan de database toevoegt, deze actie automatisch gelogd moet worden in een aparte tabel, samen met de MySQL/MSSQL gebruikersnaam van de huidige gebruiker en de huidige tijd.

    MSSQL MySQL
    CREATE TRIGGER flight_ai
    ON flight
    AFTER INSERT
    AS
        DECLARE @Count int;
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
        DECLARE flight_aiCursor CURSOR FOR
        SELECT * FROM inserted;
        OPEN flight_aiCursor;        
        FETCH NEXT FROM flight_aiCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN                    
                INSERT INTO log (ByUser, Note, EventTime)
                VALUES (
                  (SYSTEM_USER), 
                  'Record added: flight', 
                  GETDATE()
                );
                FETCH NEXT FROM flight_aiCursor;
            END
        CLOSE flight_aiCursor;
        DEALLOCATE flight_aiCursor;
    Command(s) completed successfully.
    CREATE TRIGGER flight_ai
    AFTER INSERT ON flight
    FOR EACH ROW
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (CURRENT_USER(), 'Record added: flight', NOW());
    (0 row(s) affected)
    INSERT INTO flight (FlightID, RouteID, AircraftID)
    VALUES (900, 1141, 3452);
    (1 row(s) affected)
    INSERT INTO flight (FlightID, RouteID, AircraftID)
    VALUES (900, 1141, 3452);
    (1 row(s) returned)
    SELECT * FROM log
    (1 row(s) affected)
    SELECT * FROM log
    (1 row(s) returned)
    RecordID ByUser Note EventTime
    1 1 DOMAIN\
    domainuser
    Record added: flight 2016-04-22 21:51:45.0500000
    RecordID ByUser Note EventTime
    1 1 root@% Record added: flight 2016-04-22 21:04:06

    Het is gemakkelijk om een andere trigger toe te voegen die verwijderingen aan de log toevoegd.

    MSSQL MySQL
    CREATE TRIGGER flight_ad
    ON flight
    AFTER INSERT
    AS
        DECLARE @Count int;
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
        DECLARE flight_adCursor CURSOR FOR
        SELECT * FROM inserted;
        OPEN flight_adCursor;        
        FETCH NEXT FROM flight_adCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN                    
                INSERT INTO log (ByUser, Note, EventTime)
                VALUES (
                    (SYSTEM_USER), 
                    'Record deleted: flight', 
                    GETDATE()
                );
                FETCH NEXT FROM flight_adCursor;
            END
        CLOSE flight_adCursor;
        DEALLOCATE flight_adCursor;
    Command(s) completed successfully.
    CREATE TRIGGER flight_ad
    AFTER DELETE ON flight
    FOR EACH ROW
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (
        CURRENT_USER(), 
        'Record deleted: flight', 
        NOW()
    );
    (0 row(s) affected)
    DELETE FROM flight
    WHERE flightid = 900;
    (1 row(s) affected)
    DELETE FROM flight
    WHERE flightid = 900;
    (1 row(s) affected)
    SELECT * FROM log
    (1 row(s) affected)
    SELECT * FROM log
    (1 row(s) returned)
    RecordID ByUser Note EventTime
    1 1 DOMAIN\
    domainuser
    Record added: flight 2016-04-22 21:51:45.0500000
    2 2 DOMAIN\
    domainuser
    Record deleted: flight 2016-04-24 07:39:00.3470000
    RecordID ByUser Note EventTime
    1 1 root@% Record added: flight 2016-04-22 21:04:06
    2 2 root@% Record deleted: flight 2016-04-24 07:34:17

    Het hoofdgedeelte van de trigger is niet enkel beperkt tot SQL-statements; het kan een van MSSQL/MySQL programmeerconstructies bevatten, zoals variabele definities, conditionele testen, loops, en error handlers. BEGIN en END blokken zijn verplicht wanneer het hoofdgedeelte complexe controle structuren bevat. In alle andere gevallen (bijvoorbeeld het voorgaande voorbeeld, die slechts één enkele INSERT bevat), zijn zij optioneel.

    Opmerking Om dubbelzinnigheid te vermijden, is het niet toegelaten meer dan een trigger met dezelfde trigger event en trigger-tijd per tabel. Dit betekent dat, bijvoorbeeld, dat een tabel niet twee AFTER INSERT triggers kan hebben (alhoewel het wel twee gescheiden BEFORE INSERT en AFTER INSERT triggers kan hebben). Of, om het anders te zeggen, een tabel kan hooguit zes mogelijke triggers hebben.

    Suggestie voor het benoemen van de triggers: benoem elke trigger met

  • naam van de tafel waaraan het is gekoppeld,
  • een extra suffix bestaande uit de letters a (voor "na") of b (voor "voor"), en
  • i (voor "invoegen"), u (voor "bijwerken") en d (voor "delete").
  • Dus, bijvoorbeeld, een AFTER INSERT trigger op de pax tabel zou pax_ai worden genoemd.

    Om een trigger te verwijderen, gebruikt u het DROP TRIGGER commando met de naam trekker als argument:

    MSSQL MySQL
    DROP TRIGGER flight_ad;
    Command(s) completed successfully.
    DROP TRIGGER flight_ad;
    (0 row(s) affected)

    Tip: Het laten vallen van een tabel verwijdert automatisch alle triggers die ermee verbonden zijn.

    Om het hoofgedeelte van een specifieke trigger te bekijken, gebruikt u in MySQL het commando SHOW CREATE TRIGGER <naam_trigger> en in MSSQL het commando EXEC sp_helptext [naam_trigger].

    MSSQL MySQL
    EXEC sp_helptext [flight_ad];
    Command(s) completed successfully.
    SHOW CREATE TRIGGER flight_ad;
    (1 row(s) returned)
    Text
    1 CREATE TRIGGER flight_ad
    2 ON flight
    3 AFTER INSERT
    4 AS
    5 DECLARE @Count int;
    6 SET @Count = @@ROWCOUNT
    7 IF @Count = 0
    8 RETURN;
    9 DECLARE flight_adCursor CURSOR FOR
    10 SELECT * FROM inserted;
    11 OPEN flight_adCursor;
    12 FETCH NEXT FROM flight_adCursor;
    13 WHILE @@FETCH_STATUS = 0
    14 BEGIN
    15 INSERT INTO log (ByUser, Note, EventTime)
    16 VALUES (
    17 (SYSTEM_USER),
    18 'Record deleted: flight',
    19 GETDATE()
    20 );
    21 FETCH NEXT FROM flight_adCursor;
    22 END
    23 CLOSE flight_adCursor;
    24 DEALLOCATE flight_adCursor;
    1
    Trigger flight_ad
    sql_mode STRICT_TRANS_TABLES,
    NO_AUTO_CREATE_USER,
    NO_ENGINE_SUBSTITUTION
    SQL Original Statement CREATE DEFINER=`root`@`%` TRIGGER flight_ad
    AFTER DELETE ON flight
    FOR EACH ROW
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (CURRENT_USER(), 'Record deleted: flight', NOW())
    character utf8

    Om in MySQL een lijst van alle triggers bekijken op de server, gebruikt u het SHOW TRIGGERS commando. U kunt de uitvoer van filteren deze opdracht met een WHERE-clausule.

    MSSQL MySQL
    EXEC sp_helptext [flight_ad];
    Command(s) completed successfully.
    SHOW CREATE TRIGGER flight_ad;
    (1 row(s) returned)
    Text
    1 CREATE TRIGGER flight_ad
    2 ON flight
    3 AFTER INSERT
    4 AS
    5 DECLARE @Count int;
    6 SET @Count = @@ROWCOUNT
    7 IF @Count = 0
    8 RETURN;
    9 DECLARE flight_adCursor CURSOR FOR
    10 SELECT * FROM inserted;
    11 OPEN flight_adCursor;
    12 FETCH NEXT FROM flight_adCursor;
    13 WHILE @@FETCH_STATUS = 0
    14 BEGIN
    15 INSERT INTO log (ByUser, Note, EventTime)
    16 VALUES (
    17 (SYSTEM_USER),
    18 'Record deleted: flight',
    19 GETDATE()
    20 );
    21 FETCH NEXT FROM flight_adCursor;
    22 END
    23 CLOSE flight_adCursor;
    24 DEALLOCATE flight_adCursor;
    1
    Trigger flight_ad
    sql_mode STRICT_TRANS_TABLES,
    NO_AUTO_CREATE_USER,
    NO_ENGINE_SUBSTITUTION
    SQL Original Statement CREATE DEFINER=`root`@`%` TRIGGER flight_ad
    AFTER DELETE ON flight
    FOR EACH ROW
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (CURRENT_USER(), 'Record deleted: flight', NOW())
    character utf8

    Trigger Security: DEFINER

    Het CREATE TRIGGER commando ondersteunt een extra DEFINER clausule die het gebruikersaccount aangeeft waarvan de privileges moeten worden overwogen bij het uitvoeren van de trigger. Zodat de trigger om succesvol kan uitvoeren, moet deze gebruiker alle privileges hebben die nodig zijn om de statements in het hoofdgedeelte van de trigger te kunnen uitvoeren. Standaard stelt MySQL de DEFINER in op de waarde van de gebruiker die de trigger heeft aangemaakt.

    MSSQL MySQL
    CREATE
    DEFINER = 'user@example.com'
    TRIGGER flight_ad
    AFTER DELETE ON flight
    FOR EACH ROW
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (USER(), 'Record deleted: flight', NOW());
    (0 row(s) returned)

    BEFORE Trigger vs AFTER Trigger

    Er is geen vaste regel in het gebruik van beide typen triggers. Men kan echter wel volgende vuistregel hanteren:

  • Gebruik AFTER triggers voor transacties (zoals het invoegen in een log tabel of het bijwerken van een denormalisatie)
  • Gebruik BEFORE triggers voor constraints of regels.
  • Old Values / New Values

    Het is mogelijk om binnen het hoofdgedeelte van een trigger te refereren naar de veldwaarden van zowel de before en after trigger event. Dit kan men doen door het voorvoegsel OLD of NEW voor de veldwaardenaam te plaatsen.

    UPDATE trigger:

  • het voorvoegsel OUDE geeft de pre-update waarde van de bijgewerkte velden (de initiële veldwaarde)
  • het voorvoegsel NEW geeft de post-update waarde van de bijgewerkte velden (de nieuwe, binnenkomende veldwaarde)
  • DELETE trigger:

  • alleen OLD-waarden: de pre-delete waarde van alle velden
  • INSERT trigger:

  • alleen NEW-waarden: de post-insert waarde van alle velden
  • Volgende voorbeeld registreert veranderingen in de vluchttabel en specificeert de gewijzigde waarden als onderdeel van het log bericht:

    MSSQL MySQL
    CREATE TRIGGER flight_au
    ON flight
    AFTER UPDATE
    AS
    BEGIN    
        DECLARE @Count int,
                @str varchar(255),
                @NewFlightID AS smallint,
                @OldFlightID AS smallint,
                @NewRouteID AS smallint,
                @OldRouteID AS smallint,
                @NewAircraftID AS smallint,
                @OldAircraftID AS smallint;
     
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
     
        SELECT 
            @NewFlightID = FlightID, 
            @NewRouteID = RouteID, 
            @NewAircraftID = AircraftID
        FROM inserted;
     
        SELECT 
            @OldFlightID = FlightID, 
            @OldRouteID = RouteID, 
            @OldAircraftID = AircraftID
        FROM deleted;
     
        DECLARE flight_auCursor CURSOR FOR
        SELECT * FROM inserted;
        OPEN flight_auCursor;        
        FETCH NEXT FROM flight_auCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN 
                IF @OldFlightID != @NewFlightID
                    BEGIN
                        SET @str = CONCAT(
                          @str, 
                          'FlightID ', 
                          @OldFlightID, '-> ', 
                          @NewFlightID, ' '
                        );
                    END
                IF @OldRouteID != @NewRouteID
                    BEGIN
                        SET @str = CONCAT(
                          @str, 
                          'RouteID ', 
                          @OldRouteID, '-> ', 
                          @NewRouteID, ' '
                        );
                    END
                IF @OldAircraftID != @NewAircraftID
                    BEGIN
                        SET @str = CONCAT(
                          @str, 
                          'AircraftID ', 
                          @OldAircraftID, '-> ', 
                          @NewAircraftID, ' '
                        );
                    END            
                INSERT INTO log (ByUser, Note, EventTime)
                VALUES (
                  (SYSTEM_USER), 
                  CONCAT('Record updated flight: ', 
                  @str), 
                  GETDATE()
                );
                FETCH NEXT FROM flight_auCursor;
            END
        CLOSE flight_auCursor;
        DEALLOCATE flight_auCursor;
    END
    Command(s) completed successfully.
    DELIMITER //
    CREATE TRIGGER flight_au
    AFTER UPDATE ON flight
    FOR EACH ROW
        BEGIN
            DECLARE str VARCHAR(255) DEFAULT '';
            IF OLD.FlightID != NEW.FlightID THEN
                SET str = CONCAT(
                  str, 'FlightID ', 
                  OLD.FlightID, ' -> ', 
                  NEW.FlightID, ' '
                );
            END IF;
            IF OLD.RouteID != NEW.RouteID THEN
                SET str = CONCAT(
                  str, 'RouteID ', 
                  OLD.RouteID, ' -> ', 
                  NEW.RouteID, ' '
                );
            END IF;
            IF OLD.AircraftID != NEW.AircraftID THEN
                SET str = CONCAT(
                  str, 
                  'AircraftID ', 
                  OLD.AircraftID, ' -> ', 
                  NEW.AircraftID);
            END IF;
            INSERT INTO log (ByUser, Note, EventTime)
            VALUES (
              USER(), 
              CONCAT('Record updated: flight: ', str), 
              NOW()
            );
        END //
    (0 row(s) affected)
    UPDATE flight
    SET AircraftID = 3145
    WHERE FlightID = 900;
    (1 row(s) affected)
    UPDATE flight
    SET AircraftID = 3145
    WHERE FlightID = 900;
    (1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0)
    SELECT * FROM log
    (1 row(s) affected)
    SELECT * FROM log
    (1 row(s) returned)
    RecordID ByUser Note EventTime
    1 1 DOMAIN\
    domainuser
    Record added: flight 2016-04-22 21:51:45.0500000
    2 2 DOMAIN\
    domainuser
    Record deleted: flight 2016-04-24 07:39:00.3470000
    3 3 DOMAIN\
    domainuser
    Record updated flight: AircraftID 3452-> 3145 2016-04-24 10:03:47.5400000
    RecordID ByUser Note EventTime
    1 1 root@% Record added: flight 2016-04-22 21:04:06
    2 2 root@% Record deleted: flight 2016-04-24 07:34:17
    3 3 root@% Record updated: flight: AircraftID 3452 -> 3145 2016-04-24 10:11:17

    Een complexer voorbeeld

    Bedenk dat een luchtvaartmaatschappij een beperkte hoeveelheid stoelen per vlucht en vlucht klasse heeft, en dat de hoeveelheid beschikbare zetels voor elke vlucht moet worden bijgewerkt op een continue basis als passagiers hun vlucht te boeken. Om zijn winstmarge te verhogen zou de luchtvaartmaatschappij automatisch een prijsverhoging van de tickets willen zodra de vlucht begint te vullen.

    Wijze waarop informatie wordt opgeslagen in de databank:

  • Passagier records voor elke vlucht en klasse combinatie worden opgenomen in de pax tabel.
  • De live-seat inventarisatie voor een bepaalde flight-en-class combinatie kan worden gevonden in de stats tabel.
  • de pax en stats tabellen zijn met elkaar verbonden via de gemeenschappelijke FlightID, FlightDate en ClassID velden.
  • Het maximum aantal zitplaatsen mogelijk in elke klasse van een bepaalde vlucht, samen met de basis (startende) ticketprijs, is opgenomen in de flightclass tabel.
  • Dus, bijvoorbeeld, de vlucht # 652 die opereert op de route Orly-Budapest, heeft maximaal 10 plaatsen beschikbaar in Gold klasse met een basisprijs van € 200 en 20 zitplaatsen verkrijgbaar in zilver klasse met een basisprijs van € 50.

    MSSQL MySQL
    SELECT FlightID, ClassID, MaxSeats, BasePrice
    FROM flightclass
    WHERE FlightID=652;
    (2 row(s) affected)
    SELECT FlightID, ClassID, MaxSeats, BasePrice
    FROM flightclass
    WHERE FlightID=652;
    (2 row(s) returned)
    FlightID ClassID MaxSeats BasePrice
    1 652 2 10 200
    2 652 3 20 50
    FlightID ClassID MaxSeats BasePrice
    1 652 2 10 200
    2 652 3 20 50

    Als we kijken naar de tabel met statistieken voor de vlucht op 20 januari 2009, zien we dat er momenteel 9 plaatsen beschikbaar in Gold klasse en 18 zitplaatsen beschikbaar zijn in Silver klasse. Dat wil zeggen, dat er momenteel drie passagiers momenteel gepland zijn om te vliegen op die dag.

    MSSQL MySQL
    SELECT ClassID, CurrSeats, CurrPrice 
    FROM stats 
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) affected)
    SELECT ClassID, CurrSeats, CurrPrice 
    FROM stats 
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) returned)
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 17 50
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 17 50

    Met deze informatie, wordt het mogelijk om een trigger te maken die automatisch het aantal beschikbare plaatsen in de stats tabel bijwerkt. Telkens als een passagier een vlucht boekt, wordt een nieuwe record toegevoegd aan de pax tabel. Dus op deze tabel kan een AFTER INSERT trigger worden gebruikt om automatisch het aantal beschikbare zetels met 1 te verminderen in de stats tabel bij elke invoeging van een record.

    MSSQL MySQL
    CREATE TRIGGER pax_ai
    ON pax
    AFTER INSERT
    AS
    BEGIN
        DECLARE @Count int,
                @str varchar(255),
                @NewFlightID AS smallint,
                @NewFlightDate AS date,
                @NewClassID AS smallint;
     
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
            
        SELECT 
            @NewFlightID = FlightID,
            @NewFlightDate = FlightDate,
            @NewClassID = ClassID
        FROM inserted;
     
        DECLARE pax_aiCursor CURSOR FOR
        SELECT * FROM inserted;
        OPEN pax_aiCursor;        
        FETCH NEXT FROM pax_aiCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN
                UPDATE stats
                    SET CurrSeats = CurrSeats - 1
                    WHERE FlightID = @NewFlightID
                    AND FlightDate = @NewFlightDate
                    AND ClassID = @NewClassID;
                FETCH NEXT FROM pax_aiCursor;
            END
        CLOSE pax_aiCursor;
        DEALLOCATE pax_aiCursor;
    END
    Command(s) completed successfully.
    DELIMITER //
    CREATE TRIGGER pax_ai
    AFTER INSERT ON pax
    FOR EACH ROW
        BEGIN
            UPDATE stats AS s
            SET s.CurrSeats = s.CurrSeats - 1
            WHERE s.FlightID = NEW.FlightID
            AND s.FlightDate = NEW.FlightDate
            AND s.ClassID = NEW.ClassID;
        END //
    (0 row(s) affected)

    Kijk hoe dit werkt door het invoegen van een nieuwe passagier in de pax tabel en herzie vervolgens de stats tabel.

    MSSQL MySQL
    INSERT INTO pax (
      FlightID, FlightDate, ClassID, PaxName, PaxRef
      )
    VALUES (
      652, '2009-01-20', 3, 'Igor Iguana', 'TR58304888'
      );
    (1 row(s) affected)
    INSERT INTO pax (
      FlightID, FlightDate, ClassID, PaxName, PaxRef
      )
    VALUES (
      652, '2009-01-20', 3, 'Igor Iguana', 'TR58304888'
      );
    (1 row(s) affected)
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) affected)
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) returned)
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 16 50
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 16 50

    Op dezelfde manier, elke keer dat een annulering plaatsvindt, wordt de bijbehorende record verwijderd uit de passagierslijst en een AFTER DELETE trigger kan worden gebruikt om gelijktijdig het aantal beschikbare zetels met 1 te verhogen.

    MSSQL MySQL
    CREATE TRIGGER pax_ad
    ON pax
    AFTER DELETE
    AS
    BEGIN
        DECLARE @Count int,
                @str varchar(255),
                @OldFlightID AS smallint,
                @OldFlightDate AS date,
                @OldClassID AS smallint;
     
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
            
        SELECT 
            @OldFlightID = FlightID,
            @OldFlightDate = FlightDate,
            @OldClassID = ClassID
        FROM deleted;
     
        DECLARE pax_adCursor CURSOR FOR
        SELECT * FROM deleted;
        OPEN pax_adCursor;        
        FETCH NEXT FROM pax_adCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN
                UPDATE stats
                SET CurrSeats = CurrSeats + 1
                WHERE FlightID = @OldFlightID
                AND FlightDate = @OldFlightDate
                AND ClassID = @OldClassID;
                FETCH NEXT FROM pax_adCursor;
            END
        CLOSE pax_adCursor;
        DEALLOCATE pax_adCursor;
    END
    Command(s) completed successfully.
    DELIMITER //
    CREATE TRIGGER pax_ad
    AFTER DELETE ON pax
    FOR EACH ROW
        BEGIN
            UPDATE stats AS s
            SET s.CurrSeats = s.CurrSeats + 1
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        END //
    (0 row(s) affected)

    Het automatisch verhogen (of verlagen) van het ticket prijs als het aantal stoelen vermindert (of vermeerdert) kan worden bereikt door het definiëren van verschillende niveaus van het stoelgebruik en het aanpassen van de huidige prijs naar boven of naar beneden door een vast percentage afhankelijk van het huidige niveau.

  • Bijvoorbeeld kan de luchtvaartmaatschappij besluiten dat wanneer 25 procent van de stoelen in een klasse worden verkocht, de prijs automatisch moet worden verhoogd met 50 procent.
  • Gelijkaardig, zodra 75 procent van de stoelen verkocht zijn, de prijs nogmaals verhoogd moet worden met 50 procent.
  • De trigger begint door eerst met het bijwerken van de inventaris van de zetels. Vervolgens wordt het huidige aantal beschikbare zetels, het maximaal aantal mogelijke zetels en de huidige prijs en de basisprijs voor die specifieke vlucht / klasse combinatie opgehaald. Vervolgens wordt het zetelgebruik ratio berekent en wordt de huidige prijs bijgewerkt naargelang deze verhouding tussen 25 en 75 procent of meer dan 75 procent bedraagt.

    Het toevoegen van deze logica impliceert de omzetting van de eerder gedefinieerde triggers, als volgt:

    MSSQL MySQL
    CREATE TRIGGER pax_ai
    ON pax
    AFTER INSERT
    AS
    BEGIN
        DECLARE @Count int,
                @str varchar(255),
                @NewFlightID AS smallint,
                @NewFlightDate AS date,
                @NewClassID AS smallint,
                @u AS float, 
                @cs AS float, 
                @ms AS float, 
                @bp AS float, 
                @cp AS float;
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;
        SELECT 
            @NewFlightID = FlightID,
            @NewFlightDate = FlightDate,
            @NewClassID = ClassID
        FROM inserted;         
        DECLARE pax_aiCursor CURSOR FOR
        SELECT * FROM inserted;
        OPEN pax_aiCursor;        
        FETCH NEXT FROM pax_aiCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN
                UPDATE stats
                    SET CurrSeats = CurrSeats - 1
                    WHERE FlightID = @NewFlightID
                    AND FlightDate = @NewFlightDate
                    AND ClassID = @NewClassID;
                SELECT @cs = CurrSeats, @cp = CurrPrice 
                    FROM stats
                    WHERE FlightID = @NewFlightID
                        AND FlightDate = @NewFlightDate
                        AND ClassID = @NewClassID
                SELECT @ms = MaxSeats, @bp = BasePrice
                    FROM flightclass
                    WHERE FlightID = @NewFlightID
                    AND ClassID = @NewClassID
                SET @u = 1 - (@cs/@ms)
                IF @u >= 0.25 
                    AND @u < 0.75 
                    AND @cp != ROUND((@bp * 1.5),0)
                    BEGIN
                        UPDATE stats
                            SET CurrPrice = ROUND((@bp * 1.5),0)
                            WHERE FlightID = @NewFlightID
                            AND FlightDate = @NewFlightDate
                            AND ClassID = @NewClassID;
                    END
                IF @u >= 0.75 
                    AND @cp != ROUND((@bp * 2.25),0)
                    BEGIN
                        UPDATE stats
                            SET CurrPrice = ROUND((@bp * 2.25),0)
                            WHERE FlightID = @NewFlightID
                            AND FlightDate = @NewFlightDate
                            AND ClassID = @NewClassID;
                    END                
                FETCH NEXT FROM pax_aiCursor;
            END
        CLOSE pax_aiCursor;
        DEALLOCATE pax_aiCursor;
    END
    Command(s) completed successfully.
    DELIMITER //
    CREATE TRIGGER pax_ai
    AFTER INSERT ON pax
    FOR EACH ROW
    BEGIN
        DECLARE u FLOAT DEFAULT 0;
        DECLARE cs, ms, bp, cp INT DEFAULT 0;
        UPDATE stats AS s 
            SET s.CurrSeats = s.CurrSeats - 1
            WHERE s.FlightID = NEW.FlightID
            AND s.FlightDate = NEW.FlightDate
            AND s.ClassID = NEW.ClassID;
        SELECT s.CurrSeats, s.CurrPrice INTO cs , cp 
            FROM stats AS s
            WHERE s.FlightID = NEW.FlightID
            AND s.FlightDate = NEW.FlightDate
            AND s.ClassID = NEW.ClassID;
        SELECT fc.MaxSeats, fc.BasePrice INTO ms , bp 
            FROM flightclass AS fc
            WHERE fc.FlightID = NEW.FlightID
            AND fc.ClassID = NEW.ClassID;
        SET u = 1 - (cs/ms);
        IF (u >= 0.25 
          AND u < 0.75 
          AND cp != ROUND(bp*1.5)) THEN
            UPDATE stats AS s
                SET s.CurrPrice = ROUND(bp*1.5)
                WHERE s.FlightID = NEW.FlightID
                AND s.FlightDate = NEW.FlightDate
                AND s.ClassID = NEW.ClassID;
        END IF;
        IF (u >= 0.75 AND cp != ROUND(bp*2.25)) THEN
            UPDATE stats AS s
            SET s.CurrPrice = ROUND(bp*2.25)
            WHERE s.FlightID = NEW.FlightID
            AND s.FlightDate = NEW.FlightDate
            AND s.ClassID = NEW.ClassID;
        END IF;
    END //
    (0 row(s) affected)

    Het is ook noodzakelijk om de prijs bij te actualiseren als passagiers hun reservering annuleren. Hier is de herziene AFTER DELETE trigger:

    MSSQL MySQL
    CREATE TRIGGER pax_ad
    ON pax
    AFTER DELETE
    AS
    BEGIN
        DECLARE @Count int,
                @str varchar(255),
                @OldFlightID AS smallint,
                @OldFlightDate AS date,
                @OldClassID AS smallint,
                @u AS float,
                @cs AS float, 
                @ms AS float, 
                @bp AS float, 
                @cp AS float;
        SET @Count = @@ROWCOUNT
        IF @Count = 0
            RETURN;        
        SELECT 
            @OldFlightID = FlightID,
            @OldFlightDate = FlightDate,
            @OldClassID = ClassID
        FROM deleted;
        DECLARE pax_adCursor CURSOR FOR
        SELECT * FROM deleted;
        OPEN pax_adCursor;        
        FETCH NEXT FROM pax_adCursor;
            WHILE @@FETCH_STATUS = 0 
            BEGIN
                UPDATE stats
                    SET CurrSeats = CurrSeats + 1
                    WHERE FlightID = @OldFlightID
                    AND FlightDate = @OldFlightDate
                    AND ClassID = @OldClassID;
                SELECT @cs = CurrSeats, @cp = CurrPrice 
                    FROM stats
                    WHERE FlightID = @OldFlightID
                        AND FlightDate = @OldFlightDate
                        AND ClassID = @OldClassID
                SELECT @ms = MaxSeats, @bp = BasePrice
                    FROM flightclass
                    WHERE FlightID = @OldFlightID
                    AND ClassID = @OldClassID
                SET @u = 1 - (@cs/@ms)
                IF @u < 0.25 
                    AND @cp != @bp
                    BEGIN
                        UPDATE stats
                            SET CurrPrice = @bp
                            WHERE FlightID = @OldFlightID
                            AND FlightDate = @OldFlightDate
                            AND ClassID = @OldClassID;    
                    END
                IF @u >= 0.25
                    AND @u < 0.75 
                    AND @cp != ROUND((@bp * 1.5),0)
                    BEGIN
                        UPDATE stats
                            SET CurrPrice = ROUND((@bp * 1.5),0)
                            WHERE FlightID = @OldFlightID
                            AND FlightDate = @OldFlightDate
                            AND ClassID = @OldClassID;
                    END
                IF @u >= 0.75 
                    AND @cp != ROUND((@bp * 2.25),0)
                    BEGIN
                        UPDATE stats
                            SET CurrPrice = ROUND((@bp * 2.25),0)
                            WHERE FlightID = @OldFlightID
                            AND FlightDate = @OldFlightDate
                            AND ClassID = @OldClassID;
                    END
                FETCH NEXT FROM pax_adCursor;
            END
        CLOSE pax_adCursor;
        DEALLOCATE pax_adCursor;
    END
    Command(s) completed successfully.
    DELIMITER //
    CREATE TRIGGER pax_ad
    AFTER DELETE ON pax
    FOR EACH ROW
    BEGIN
        DECLARE u FLOAT DEFAULT 0;
        DECLARE cs, ms, bp, cp INT DEFAULT 0;
        UPDATE stats AS s
            SET s.CurrSeats = s.CurrSeats + 1
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        SELECT s.CurrSeats, s.CurrPrice INTO cs, cp
            FROM stats AS s
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        SELECT fc.MaxSeats, fc.BasePrice INTO ms, bp
            FROM flightclass AS fc
            WHERE fc.FlightID = OLD.FlightID
            AND fc.ClassID = OLD.ClassID;
        SET u = 1 - (cs/ms);
        IF (u < 0.25 AND cp != bp) THEN
            UPDATE stats AS s
            SET s.CurrPrice = bp
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        END IF;
        IF (u >= 0.25 AND u < 0.75
        AND cp != ROUND(bp*1.5)) THEN
            UPDATE stats AS s
            SET s.CurrPrice = ROUND(bp*1.5)
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        END IF;
        IF (u >= 0.75
        AND cp != ROUND(bp*2.25)) THEN
            UPDATE stats AS s
            SET s.CurrPrice = ROUND(bp*2.25)
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        END IF;
    END //
    (0 row(s) affected)

    Laten we proberen twee passagiers te boeken in de Gold klasse op die vlucht. Doordat er 7 van de 10 beschikbare zetels zijn geboekt, is de 25 procent drempel overschreden en zal er automatisch een prijsstijging plaatsvinden. Kijk in de tabel met statistieken, en je zult zien dat de prijs van het ticket voor de vlucht in Gold klasse is gestegen met 50 procent, van € 200 naar € 300.

    MSSQL MySQL
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) affected)
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) returned)
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 16 50
    ClassID CurrSeats CurrPrice
    1 2 9 200
    2 3 16 50
    INSERT INTO pax (
        FlightID, FlightDate, ClassID, PaxName, PaxRef
        ) 
    VALUES 
        (652, '2009-01-20', 2,'Gerry Giraffe', 'TR75950888'),
        (652, '2009-01-20', 2,'Adam Anteater', 'TR88404015');
    (2 row(s) affected)
    INSERT INTO pax (
        FlightID, FlightDate, ClassID, PaxName, PaxRef
        ) 
    VALUES 
        (652, '2009-01-20', 2,'Gerry Giraffe', 'TR75950888'),
        (652, '2009-01-20', 2,'Adam Anteater', 'TR88404015');
    (2 row(s) affected Records: 2 Duplicates: 0 Warnings: 0)
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) affected)
    SELECT ClassID, CurrSeats, CurrPrice
    FROM stats
    WHERE FlightID=652 AND FlightDate = '2009-01-20';
    (2 row(s) returned)
    ClassID CurrSeats CurrPrice
    1 2 7 300
    2 3 16 50
    ClassID CurrSeats CurrPrice
    1 2 7 300
    2 3 16 50

    Triggers and Constraints

    Zoals je misschien opgemerkt zal hebben, is mogelijk om passagiers toe te blijven voegen zelfs als de zetelinventaris negatief wordt. Dit is in theoretische zin mogelijk, een negatieve zetel inventaris zou als overboeking beschouwd kunnen worden, wat tegenwoordig een vrij algemene praktijk is van de luchtvaartmaatschappijen. Laten we aannemen dat we voor onze maatschappij ten minste, geen negatieve zetelinventaris willen.

    Dit kan gebeuren omdat de trigger in het vorige voorbeeld geen controles uitvoert op het aantal beschikbare zetels, zodat voorkomen wordt dat beschikbare aantal zetels onder nul komt of boven het maximum aantal zetels. De trigger moet worden bijgewerkt om te controleren op deze boven- en ondergrens, en de INSERT in de pax tabel te verhinderen indien deze beperkingen worden geschonden.

    En daarin ligt het probleem. In tegenstelling tot Oracle, waarmee u een trigger met de "RAISE APPLICATION ERROR" statement kan afbreken, biedt MySQL momenteel geen mechanisme om een trigger af te breken of om in de event een fout op te werpen dat er aan een door de gebruiker opgegeven beperking niet wordt voldaan. Dit is een belangrijke beperking van de MySQL's huidige implementatie van triggers. Dit heeft gezorgd voor een groot aantal discussies in de MySQL gebruikerforums, evenals creatieve oplossingen!

    Oplossing: het genereren van een MySQL fout door het uitvoeren van een illegale operatie

    Het fundamentele principe van deze tijdelijke oplossing is eenvoudig: Het opzettelijk genereren van een MySQL fout door het uitvoeren van een illegale bewerking, waardoor MySQL wordt gedwiongen de uitvoering van de trigger af te breken. Er zijn verschillende manieren waarop dit kan worden gedaan, zoals:

  • Het invoegen van een waarde in een niet bestaand veld
  • Het invoegen van een NULL waarde in een veld met de NOT NULL constraint
  • Het oproepen van een niet-bestaande opgeslagen routine
  • Het resultaat van al deze operaties is hetzelfde: een fatale fout, die MySQL zal zorgen dat MySQL de uitvoering van het statement de fout heeft veroorzaakt wordt beëindigd. Als dit statement in een BEFORE-trigger wordt geplaatst, zal de resulterende fout MySQL dwingen de trigger uitvoering af te breken, evenals het INSERT, UPDATE of DELETE statement dat verondersteld wordt om te volgen.

    Illustratie van de tijdelijke oplossing: een trigger die alleen toestaat nieuwe luchthavens als ze minstens drie start- en landingsbanen

    Opzettelijke fout die wordt gegenereerd, veroorzaakt het falen van de INSERT in zijn geheel.

    MSSQL MySQL
    ;
    (2 row(s) affected)
    DELIMITER //
    CREATE TRIGGER airport_bi
    BEFORE INSERT
    ON airport
    FOR EACH ROW
    BEGIN
        IF NEW.NumRunways < 3 THEN
            CALL i_dont_exist;
        END IF;
    END //
    (0 row(s) affected)
    ;
    (2 row(s) affected)
    INSERT INTO airport (
        AirportID, AirportCode, AirportName, CityName, 
        CountryCode, NumRunways, NumTerminals
        )
    VALUES (207, 'LTN', 'Luton Airport', 'London', 'GB', 2, 1);
    (Error Code: 1305. PROCEDURE db1.i_dont_exist does not exist)

    Laten we nu gebruik maken van een paar BEFORE triggers in de pax tabel om de beperkingen af te dwingen die werden besproken aan het begin van dit hoofdstuk.

    MSSQL MySQL
    ;
    (2 row(s) affected)
    DELIMITER //
    CREATE TRIGGER pax_bi
    BEFORE INSERT
    ON pax
    FOR EACH ROW
    BEGIN
        DECLARE cs INT DEFAULT 0;
        SELECT s.CurrSeats INTO cs
            FROM stats AS s
            WHERE s.FlightID = NEW.FlightID
            AND s.FlightDate = NEW.FlightDate
            AND s.ClassID = NEW.ClassID;
        IF cs <= 0 THEN
            SET @trigger_error := 'No seats available';
            CALL i_dont_exist();
        END IF;
    END //
    (0 row(s) affected)
    ;
    (2 row(s) affected)
    DELIMITER //    
    CREATE TRIGGER pax_bd
    BEFORE DELETE
    ON pax
    FOR EACH ROW
    BEGIN
        DECLARE cs, ms INT DEFAULT 0;
        SELECT s.CurrSeats INTO cs
            FROM stats AS s
            WHERE s.FlightID = OLD.FlightID
            AND s.FlightDate = OLD.FlightDate
            AND s.ClassID = OLD.ClassID;
        SELECT fc.MaxSeats INTO ms
            FROM flightclass AS fc
            WHERE fc.FlightID = OLD.FlightID
            AND fc.ClassID = OLD.ClassID;
        IF cs >= ms THEN
            SET @trigger_error :=
            'Cannot increase seat count';
            CALL i_dont_exist();
        END IF;
    END //
    (0 row(s) affected)