Тригерът представлява множество от SQLзаявки, съхранени под определено име в каталог в базата данни, но за разлика от процедурата, този обект е асоцииран с определена реална таблица от базата. Под реална се разбира – перманентна таблица, а не временна. Тригерите спомагат за запазването на интегритет на базата данни. Активират се при настъпване на определено събитие върху таблицата, с която са асоциирани, като това събитие е свързано с промяна на данните в нея. Тоест, тригерът се „активира“ (fire), когато настъпи събитие от тип INSERT, UPDATE, DELETE- това са трите операции, при които се променят данните в една таблица. Тригерите много приличат на съхранени процедури, но за разлика от тях не могат да бъдат извиквани собственоръчно или пък да се извикват един друг. От това, че те се активират при опит за промяна на данни в таблиците, тези обекти в БД намират широко приложение при валидация на данни, проверка на определени бизнес правила на ниво БД, извършване на елементарни преобразувателни операции върху данните, преди или след като бъдат вкарани, както и за извършване на лог върху всички промени по дадена таблица. Като недостатък на използването на тригери можем да посочим, че изпълнението им остава скрито за приложението, което ползва базата. При таблици, при които често се модифицират данните, би довело до overhead на базата.
За да си изясните как работят тригерите, трябва да се запознаете с някои понятия: trigger time, trigger event, trigger action. Първото показва кога да се извика тригерът – преди/след определена операция(insert, update, delete), наречена trigger event. Trigger action е всъщност действието, което ще извършва тригерът. Предстои да разгледаме и възможните стойности на тези три характеристики на тригерите:
- trigger time –Може да бъде BEFORE или AFTER.
- trigger event – Може да бъде INSERT, UPDATE, DELETE.
- trigger action– Може да бъде съвкупност от SQL заявки.
Жизнен цикъл:
Тригерът се „активира“ всеки път при настъпване на неговия trigger event, по време на неговия trigger timе. Проверява се дадено условие и ако то е истина, се изпълнява зададения trigger action.
Синтаксис:
CREATE TRIGGER <trigger_name> <trigger time> <trigger event> ON <table>
FOR EACH ROW <trigger action>;
FOR EACH ROW – използва се, за да специфицира, че този trigger action ще трябва да се извърши по отделно за всички редове, засегнати от операцията, поради която тригерът е възникнал. В тялото на тригера (за повече от една заявки то е заключено между begin и end) можем да използваме състоянието на данните преди и след операцията. За достъп до стойност преди операцията се използва OLD.<име на колона>, за да вземете новата стойност, използвайте NEW<име на колона>.
Нека да създадем един тригер, който прави лог на всички промени, направени по таблицата salarypayments от нашата база данни. За целта първо ще създадем една лог таблица, в която да се отразяват всички промени на оригиналната:
use school_sport_clubs;
drop table if exists salarypayments_log;
create table salarypayments_log(
id int auto_increment primary key,
operation ENUM('INSERT','UPDATE','DELETE') not null,
old_coach_id int,
new_coach_id int,
old_month int,
new_month int,
old_year int,
new_year int,
old_salaryAmount decimal,
new_salaryAmount decimal,
old_dateOfPayment datetime,
new_dateOfPayment datetime,
dateOfLog datetime
)Engine = Innodb;
Създаваме тригера:
delimiter |
CREATE TRIGGER after_salarypayment_update AFTER UPDATE ON salarypayments
FOR EACH ROW
BEGIN
INSERT INTO salarypayments_log(operation,
old_coach_id,
new_coach_id,
old_month,
new_month,
old_year,
new_year,
old_salaryAmount,
new_salaryAmount,
old_dateOfPayment,
new_dateOfPayment,
dateOfLog)
VALUES ('UPDATE',OLD.coach_id,NEW.coach_id,OLD.month,NEW.month,
OLD.year,NEW.year,OLD.salaryAmount,NEW.salaryAmount,OLD.dateOfPayment,NEW.dateOfPayment,NOW());
END;
|
Delimiter ;
Правим една заявка update:
UPDATE `salarypayments` SET `salaryAmount`='2000' WHERE `id`='13';
И виждаме какво се е случило в таблицата с логове:
Изглежда излишно дублираме и непроменената информация, затова ще пренапишем тригера, така че да проверява дали има промяна в дадено поле и ако има тогава да му вмъква новата стойност, ако не – старата е достатъчна.
DROP TRIGGER if exists after_salarypayment_update;
delimiter |
CREATE TRIGGER after_salarypayment_update AFTER UPDATE ON salarypayments
FOR EACH ROW
BEGIN
INSERT INTO salarypayments_log(operation,
old_coach_id,
new_coach_id,
old_month,
new_month,
old_year,
new_year,
old_salaryAmount,
new_salaryAmount,
old_dateOfPayment,
new_dateOfPayment,
dateOfLog)
VALUES ('UPDATE',
OLD.coach_id,
CASE NEW.coach_id WHEN OLD.coach_id THEN NULL ELSE NEW.coach_id END,
OLD.month,
CASE NEW.month WHEN OLD.month THEN NULL ELSE NEW.month END,
OLD.year,
CASE NEW.year WHEN OLD.year THEN NULL ELSE NEW.year END,
OLD.salaryAmount,
CASE NEW.salaryAmount WHEN OLD.salaryAmount THEN NULL ELSE NEW.salaryAmount END,
OLD.dateOfPayment,
CASE NEW.dateOfPayment WHEN OLD.dateOfPayment THEN NULL ELSE NEW.dateOfPayment END,
NOW());
END;
|
Delimiter ;
Нека сега да извършим промяна на един месец с друг:
UPDATE `school_sport_clubs`.`salarypayments` SET `month`='4' WHERE `id`='15';
И да видим какво имаме в лог таблицата:
Дефинирайте тригери и за останалите две операции.
Особености на тригерите:
- Ако изтриете таблица, то с нея се изтриват всички тригери, които са създадени за нея.
- Всеки тригер трябва да има уникално име.
- До версия 5.7.2 не можем да създаваме повече от един тригер за един и същи action time и trigger event. За по-високи версии това вече е възможно.
- При INSERT тригерите можем да ползваме само NEW, тъй като стара стойност просто няма.
- Аналогично е при DELETE – може да ползваме само
- Стойността на колона, достъпена с OLD е read-only.
- В BEFORE тригер можем да модифицираме стойността на <column>, но в AFTER това е безсмислено.
- В BEFORE тригер стойността на auto_increment колона е 0.
- Тригерите не трябва да съдържат изрази, с които се управлява транзакция: START TRANSACTION, COMMIT, ROLLBACK.
- Тригерите не се активират при каскадни операции.
Нека да направим тригер, който предотвратява въвеждането на отрицателна заплата в таблицата salarypayments. Ако някой се опита да въведе отрицателна заплата, то заплатата на треньора да стане 0.
delimiter | create trigger before_salarypayments_insert BEFORE INSERT ON salarypayments FOR EACH row BEGIN IF (NEW.salaryAmount < 0) THEN SET NEW.salaryAmount = 0; END IF; END; | delimiter ;
Бърз тест:
INSERT INTO `school_sport_clubs`.`salarypayments`
(`coach_id`, `month`, `year`, `salaryAmount`, `dateOfPayment`)
VALUES ('4', '4', 2016, '-1450', '2016-04-22 11:45:08');
Резултат:
Направете тригер, който пък реагира при UPDATE и при подадена отрицателна сума за update, той запазва сумата такава, каквато е била до сега.
За да видите всички създадени тригери може да използвате
SHOW TRIGGERS;
Даниел Джолев