* Тригери
Публикувано на 01 април 2012 в раздел Бази от Данни.
Тригерите са начин за автоматизиране на действия, свързани с обработката на информация в базите от данни. Те са процедури, които се изпълняват при извикване на заявки insert, update или delete. Ако приемем изпълнението на такива заявки за „събитие“, то тригерите са процедури, които се изпълняват преди или след дадено събитие.
Нека покажем един пример – ще създадем таблица с няколко футболни отбора:
CREATE DATABASE football;
USE football;
CREATE TABLE clubs(
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
points TINYINT UNSIGNED NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO clubs(name, points)
VALUES ('Славия', 0),
('ЦСКА', 0),
('Левски', 0);
И таблица с възможните мачове помежду им:
CREATE TABLE matches(
hostID TINYINT UNSIGNED NOT NULL,
FOREIGN KEY (hostID) REFERENCES clubs(id)
ON DELETE CASCADE ON UPDATE CASCADE,
guestID TINYINT UNSIGNED NOT NULL,
FOREIGN KEY (guestID) REFERENCES clubs(id)
ON DELETE CASCADE ON UPDATE CASCADE,
hostGoals TINYINT UNSIGNED NULL DEFAULT NULL,
guestGoals TINYINT UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY(hostID, guestID)
)ENGINE=InnoDB;
INSERT INTO matches(hostID, guestID)
SELECT host.id AS dname, guest.id AS gname
FROM clubs AS host JOIN clubs AS guest
ON host.id <> guest.id;
Преди започването на турнира точките на отборите са 0, а мачовете все още не са изиграни, т.е. стойностите за „голове на домакина“ (hostGoals) и „голове на госта“ (guestGoals) не са попълнени:
mysql> SELECT * FROM clubs; +----+--------+--------+ | id | name | points | +----+--------+--------+ | 1 | Славия | 0 | | 2 | ЦСКА | 0 | | 3 | Левски | 0 | +----+--------+--------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM matches; +--------+---------+-----------+------------+ | hostID | guestID | hostGoals | guestGoals | +--------+---------+-----------+------------+ | 1 | 2 | NULL | NULL | | 1 | 3 | NULL | NULL | | 2 | 1 | NULL | NULL | | 2 | 3 | NULL | NULL | | 3 | 1 | NULL | NULL | | 3 | 2 | NULL | NULL | +--------+---------+-----------+------------+ 6 rows in set (0.00 sec)
Сега искаме да реализираме следната функционалност – когато два отбора изиграят даден мач, то ние ще записваме резултата между тях, а след това ще обновяваме точките (3 за победа, 1 за равенство и нищо за загуба). Бихме могли да направим това с две заявки – една UPDATE заявка за таблица matches и една UPDATE заявка за таблица clubs. Двете обаче са свързани и зависими помежду си, следователно можем да осъществим обновяването на точките чрез тригер:
DELIMITER //
CREATE TRIGGER pointsUpdate
AFTER UPDATE ON matches FOR EACH ROW
BEGIN
IF NEW.hostGoals > NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = OLD.hostID;
ELSEIF NEW.hostGoals < NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = OLD.guestID;
ELSE UPDATE clubs
SET clubs.points = clubs.points + 1
WHERE clubs.id = OLD.hostID
OR clubs.id = OLD.guestID;
END IF;
END;//
DELIMITER ;
Виждате, че може да се възползвате от всичко, което беше налично при съхранените процедури. Ключовите думи NEW и OLD означават съответно „нова“ и „стара“ стойност. В случая когато четем стойностите hostGoals и guestGoals задължително трябва да взимаме новите им стойности (старите са били NULL). За hostID и guestID няма значение дали ще използваме NEW или OLD, защото тези колони няма да ги променяме с update заявките. Важно е да се отбележи, че OLD стойностите са само за четене, докато при нужда NEW могат да се променят от тригера.
Нека изпробваме – нека се е изиграл първият мач между Славия и ЦСКА с резултат 1:0, втория между Славия и Левски 2:2, а третия между Левски и ЦСКА 0:2:
UPDATE matches SET hostGoals=1, guestGoals=0 WHERE hostID=1 AND guestID=2; UPDATE matches SET hostGoals=2, guestGoals=2 WHERE hostID=1 AND guestID=3; UPDATE matches SET hostGoals=0, guestGoals=2 WHERE hostID=3 AND guestID=2;
Да видим мачовете и съответното класиране:
mysql> SELECT * FROM matches; +--------+---------+-----------+------------+ | hostID | guestID | hostGoals | guestGoals | +--------+---------+-----------+------------+ | 1 | 2 | 1 | 0 | | 1 | 3 | 2 | 2 | | 2 | 1 | NULL | NULL | | 2 | 3 | NULL | NULL | | 3 | 1 | NULL | NULL | | 3 | 2 | 0 | 2 | +--------+---------+-----------+------------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM clubs; +----+--------+--------+ | id | name | points | +----+--------+--------+ | 1 | Славия | 4 | | 2 | ЦСКА | 3 | | 3 | Левски | 1 | +----+--------+--------+ 3 rows in set (0.00 sec)
Така създаденият тригер от примера ще свърши отредената му работа, но е далеч от „перфектен“. Ако например сте направили грешка и сте въвели даден резултат невярно, то може би ще искате впоследствие да го поправите. Да, но всяка UPDATE заявка ще обновява класирането, т.е. ако например сме решили, че Славия е победила ЦСКА с 1:0, а после сме се поправили и сме променили резултата като 2:0, то с горния тригер ще се получи така, че Славия ще е получила 6 точки от този мач, а не 3. Именно за такива ситуации трябва или много да внимавате, или да предвиждате подобни действия:
DROP TRIGGER pointsUpdate;
DELIMITER //
CREATE TRIGGER pointsUpdate
AFTER UPDATE ON matches FOR EACH ROW
BEGIN
IF OLD.hostGoals IS NOT NULL
AND OLD.guestGoals IS NOT NULL
THEN IF OLD.hostGoals > OLD.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points - 3
WHERE clubs.id = OLD.hostID;
ELSEIF OLD.hostGoals < OLD.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points - 3
WHERE clubs.id = OLD.guestID;
ELSE UPDATE clubs
SET clubs.points = clubs.points - 1
WHERE clubs.id = OLD.hostID
OR clubs.id = OLD.guestID;
END IF;
END IF;
IF NEW.hostGoals > NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = OLD.hostID;
ELSEIF NEW.hostGoals < NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = OLD.guestID;
ELSE UPDATE clubs
SET clubs.points = clubs.points + 1
WHERE clubs.id = OLD.hostID
OR clubs.id = OLD.guestID;
END IF;
END;//
DELIMITER ;
Така вече намаляваме точките ако вече има записан резултат в системата преди да правим обновяване. Проверете горния тригер с различни примери (*).
(*) Все още не сме се предпазили напълно от невалидни данни. Ако например се въведе резултат (X, NULL) или (NULL, X) в системата (т.е. една от стойностите hostGoals и guestGoals е NULL), то със сигурност хем мачът ще е невалиден, хем точките ще се обновят погрешно. Един начин да поправим това е като създадем тригери, които ще променят стойностите ПРЕДИ обновяването на данните, като ако една от двете (но НЕ И ДВЕТЕ) е NULL, то ще я променим на 0:
DELIMITER //
CREATE TRIGGER matchValidatorInsert
BEFORE INSERT ON matches FOR EACH ROW
BEGIN
IF NEW.hostGoals IS NULL
AND NEW.guestGoals IS NOT NULL
THEN SET NEW.hostGoals = 0;
END IF;
IF NEW.guestGoals IS NULL
AND NEW.hostGoals IS NOT NULL
THEN SET NEW.guestGoals = 0;
END IF;
END; //
DELIMITER ;
DELIMITER //
CREATE TRIGGER matchValidatorUpdate
BEFORE UPDATE ON matches FOR EACH ROW
BEGIN
IF NEW.hostGoals IS NULL
AND NEW.guestGoals IS NOT NULL
THEN SET NEW.hostGoals = 0;
END IF;
IF NEW.guestGoals IS NULL
AND NEW.hostGoals IS NOT NULL
THEN SET NEW.guestGoals = 0;
END IF;
END; //
DELIMITER ;
Задача 1. Направете тригер clubsAdd, който при въвеждане на нов футболен клуб в таблица clubs добавя редове с възможните му мачове с всички други отбори в таблица matches, като головете и за домакин и за гост са NULL.
Решение: Ще се възползваме от фактът, че hostGoals и guestGoals са ‘null default null’ и ще използваме стойностите им по подразбиране (null):
DELIMITER //
CREATE TRIGGER clubsAdd
AFTER INSERT ON clubs FOR EACH ROW
BEGIN
INSERT INTO matches(hostID, guestID)
SELECT host.id AS dname, guest.id AS gname
FROM clubs AS host JOIN clubs AS guest
ON host.id <> guest.id
AND( host.id = NEW.id
OR
guest.id = NEW.id
);
END;//
DELIMITER ;
Задача 2. Направете тригер matchDelete, който при изтриване на даден футболен мач от програмата (таблица matches) обновява точките на отборите в таблица clubs, но само ако резултатът от този мач е бил различен от (NULL, NULL).
Решение:
DELIMITER //
CREATE TRIGGER matchDelete
AFTER DELETE ON matches FOR EACH ROW
BEGIN
IF OLD.hostGoals IS NOT NULL
OR OLD.guestGoals IS NOT NULL
THEN
IF OLD.hostGoals > OLD.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points - 3
WHERE clubs.id = OLD.hostID;
ELSEIF OLD.hostGoals < OLD.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points - 3
WHERE clubs.id = OLD.guestID;
ELSE UPDATE clubs
SET clubs.points = clubs.points - 1
WHERE clubs.id = OLD.hostID
OR clubs.id = OLD.guestID;
END IF;
END IF;
END;//
DELIMITER ;
Задача 3. Направете тригер matchesAdd, който при добавяне на нови мачове в програмата в таблица matches (например разширяваме турнира с нови мачове или ако сме изтрили даден мач и после сме го вмъкнали отново) прави съответните обновявания на точките в таблица clubs (само ако при INSERT заявката са указани голове – ако резултатите от добавените мачове са с null стойности, то няма нужда да се прави!). Забележете, че за да можете да добавяте нови мачове, то или преди това трябва да изтриете съществуващ мач или да премахнете PRIMARY KEY от таблица matches – в създадената по-горе таблица не е възможно да въведете един и същи мач повече от два пъти.
Решение:
DELIMITER //
CREATE TRIGGER extraMatchAdd
AFTER INSERT ON matches FOR EACH ROW
BEGIN
IF NEW.hostGoals IS NOT NULL
AND NEW.guestGoals IS NOT NULL
THEN IF NEW.hostGoals > NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = NEW.hostID;
ELSEIF NEW.hostGoals < NEW.guestGoals
THEN UPDATE clubs
SET clubs.points = clubs.points + 3
WHERE clubs.id = NEW.guestID;
ELSE UPDATE clubs
SET clubs.points = clubs.points + 1
WHERE clubs.id = NEW.hostID
OR clubs.id = NEW.guestID;
END IF;
END IF;
END;//
DELIMITER ;
Внимание (!!!). Очевидно тук тригерът от задача 1 (който добавя редове в таблица matches при добавяне на ред в таблица clubs) би задействал и тригерът от задача 3 (който обновява таблица clubs при добавяне на редове в таблица matches). За щастие в този конкретен при случай тригерът в задача 3 няма да направи нищо и няма да има проблем. При всички случаи обаче трябва изключително много да внимавате в такива ситуации (когато един тригер активира друг) . Ето ви един пример, в който се получава „патова ситуация“ и изпълнението не може да продължи:
CREATE TABLE a(
ID INT
);
CREATE TABLE b(
ID INT
);
DELIMITER //
CREATE TRIGGER proba
AFTER INSERT ON a FOR EACH ROW
BEGIN
INSERT INTO b(ID)
VALUES (NEW.ID);
END; //
DELIMITER ;
DELIMITER //
CREATE TRIGGER proba2
AFTER INSERT ON b FOR EACH ROW
BEGIN
INSERT INTO a(ID)
VALUES (NEW.ID);
END; //
DELIMITER ;
mysql> INSERT INTO a(ID) VALUES (1);
ERROR 1442 (HY000): Can't update table 'a' in stored
function/trigger because it is already used by statement
which invoked this stored function/trigger.
mysql> INSERT INTO b(ID) VALUES (1);
ERROR 1442 (HY000): Can't update table 'b' in stored
function/trigger because it is already used by statement
which invoked this stored function/trigger.
Освен това ако използвате тригери, то вече няма да може да може да използвате вложени заявки, които извличат информация от таблицата, която ще бъде обновявана:
mysql> UPDATE matches
-> SET hostGoals=1, guestGoals=1
-> WHERE hostID=(
-> SELECT id FROM clubs WHERE name="Левски"
-> )
-> AND
-> guestID=(
-> SELECT id FROM clubs WHERE name="Славия"
-> );
ERROR 1442 (HY000): Can't update table 'clubs' in stored
function/trigger because it is already used by statement
which invoked this stored function/trigger
Затова винаги планирайте тригерите си много внимателно.
Важно: Поне до версия 5.5 на MySQL тригерите НЕ се активират при каскадно изтриване/обновяване на данни.
Най-лесно е да го направиш и да видиш.
Иначе вмъква всички възможни комбинации от мачове, но със стойност (NULL,NULL) като резултат.
Пояснение: резултатната таблица от SELECT заявката се използва като VALUES за INSERT заявката.
Виж тук: https://www.cphpvb.net/db/8017-self-join/
INSERT INTO matches(hostID, guestID)
SELECT host.id AS dname, guest.id AS gname
FROM clubs AS host JOIN clubs AS guest
ON host.id <> guest.id;
Какво точно прави тази инсерт?
ON host.id <> guest.id; – тук каква роля играе?
В заявката имаме „AS host“ и „AS guest“. Те са таблицата „clubs“ с ново име. Виж статията на тема „Self join“.
Имам един въпрос тук в тази заявка SELECT host.id AS dname, guest.id AS gname
FROM clubs AS host JOIN clubs AS guest
от къде се появиха тези host и guest като ги няма дефинирани в create table заявката на club има само id поле няма host и guest?
На 2 ра задача
DELIMITER //
CREATE TRIGGER matchDelete
AFTER DELETE ON matches FOR EACH ROW
BEGIN
IF OLD.hostGoals IS NOT NULL
OR OLD.guestGoals IS NOT NULL
Не трябва ли да е AND, тъй като искаме да проверяваме при (!NULL,!NULL), а не при (!NULL,NULL) или (NULL,!NULL)?
Благодаря
Зависи как третираш вариантите (!NULL,NULL) и (NULL,!NULL). В случая опираме до интерпретации.