Triggers

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

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.

INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (900, 1141, 3452);

(1 row(s) affected)

SELECT * FROM log

(1 row(s) affected)

	RecordID	ByUser	Note	EventTime
1	1	DOMAIN\ domainuser	Record added: flight	2016-04-22 21:51:45.0500000

MySQL

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

SELECT * FROM log

(1 row(s) returned)

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

MSSQL

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.

DELETE FROM flight
WHERE flightid = 900;

(1 row(s) affected)

SELECT * FROM log

(1 row(s) affected)

	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

MySQL

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)

SELECT * FROM log

(1 row(s) returned)

	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:

MS SQL

DROP TRIGGER flight_ad;

Command(s) completed successfully.

MySQL

DROP TRIGGER flight_ad;

(0 row(s) affected)

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

EXEC sp_helptext [flight_ad];

Command(s) completed successfully.

	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;

MySQL

SHOW CREATE TRIGGER flight_ad;

(1 row(s) returned)

	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

EXEC sp_helptext [flight_ad];

Command(s) completed successfully.

	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;

MySQL

SHOW CREATE TRIGGER flight_ad;

(1 row(s) returned)

	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

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.

UPDATE flight
SET AircraftID = 3145
WHERE FlightID = 900;

(1 row(s) affected)

SELECT * FROM log

(1 row(s) affected)

	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

MySQL

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 Rows matched: 1 Changed: 1 Warnings: 0)

SELECT * FROM log

(1 row(s) returned)

	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

SELECT FlightID, ClassID, MaxSeats, BasePrice
FROM flightclass
WHERE FlightID=652;

(2 row(s) affected)

HTML clipboard

	FlightID	ClassID	MaxSeats	BasePrice
1	652	2	10	200
2	652	3	20	50

MySQL

SELECT FlightID, ClassID, MaxSeats, BasePrice
FROM flightclass
WHERE FlightID=652;

(2 row(s) returned)

HTML clipboard

	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

SELECT ClassID, CurrSeats, CurrPrice 
FROM stats 
WHERE FlightID=652 AND FlightDate = '2009-01-20';

(2 row(s) affected)

	ClassID	CurrSeats	CurrPrice
1	2	9	200
2	3	17	50

MYSQL

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

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

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.

MySQL

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

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)

	ClassID	CurrSeats	CurrPrice
1	2	9	200
2	3	16	50

MySQL

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

	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

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.

MySQL

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

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.

MySQL

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

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.

MySQL

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

SELECT ClassID, CurrSeats, CurrPrice
FROM stats
WHERE FlightID=652 AND FlightDate = '2009-01-20';

(2 row(s) affected)

HTML clipboard

	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)

SELECT ClassID, CurrSeats, CurrPrice
FROM stats
WHERE FlightID=652 AND FlightDate = '2009-01-20';

(2 row(s) affected)

HTML clipboard

	ClassID	CurrSeats	CurrPrice
1	2	7	300
2	3	16	50

MySQL

SELECT ClassID, CurrSeats, CurrPrice
FROM stats
WHERE FlightID=652 AND FlightDate = '2009-01-20';

(2 row(s) returned)

HTML clipboard

	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 Records: 2 Duplicates: 0 Warnings: 0)

SELECT ClassID, CurrSeats, CurrPrice
FROM stats
WHERE FlightID=652 AND FlightDate = '2009-01-20';

(2 row(s) returned)

HTML clipboard

	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

;

(2 row(s) affected)

;

(2 row(s) affected)

MySQL

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)

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

;

(2 row(s) affected)

;

(2 row(s) affected)

MySQL

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)

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)

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)