Како да користите предизвикувачи во MySQL

Авторот ја избра програмата Пишувај за донации.

Вовед

Кога работите со релациони бази на податоци и UPDATE.

Сепак, SQL базите на податоци може да добијат и инструкции да вршат однапред дефинирани дејства автоматски секогаш кога ќе се случи одреден настан преку предизвикувачи. На пример, можете да користите предизвикувачи за да го зачувате дневникот на ревизорската патека на сите изјави DELETE или автоматски да ги ажурирате збирните статистички резимеа секогаш кога редовите се ажурираат или се додаваат на табелата.

Во ова упатство, ќе користите различни SQL предизвикувачи за автоматско извршување на дејства каде што редовите се вметнуваат, ажурираат или бришат.

Предуслови

За да го следите ова упатство, ќе ви треба компјутер со систем за управување со релациона база на податоци (RDBMS) базиран на SQL. Инструкциите и примерите во ова упатство беа потврдени со користење на следната средина:

  • Сервер кој работи на Ubuntu 20.04, со корисник кој не е root со административни привилегии и заштитен ѕид конфигуриран со UFW, како што е опишано во нашиот првичен водич за поставување сервер за Ubuntu 20.04.
  • MySQL е инсталиран и заштитен на серверот, како што е наведено во чекор 3.
  • Основно познавање со извршувањето на прашањата SELECT, INSERT, UPDATE и DELETE за манипулирање со податоците во базата на податоци како што е опишано во нашите водичи како да се бришат податоци во SQL.
  • Основно познавање со користење на вгнездени прашања како што е опишано во нашиот водич Како да се користат вгнездени прашања во SQL.
  • Основно познавање со користење на збирни математички функции како што е опишано во нашиот водич Како да се користат математички изрази и збирни функции во SQL.

Забелешка: Многу RDBMS користат сопствена имплементација на SQL. Иако предизвикувачите се спомнуваат како дел од стандардот SQL, стандардот не ја спроведува нивната синтакса или строгиот начин на нивна имплементација. Како резултат на тоа, нивната имплементација се разликува во различни бази на податоци. Командите наведени во ова упатство ја користат синтаксата за базата на податоци MySQL и може да не работат на други мотори на бази на податоци.

Ќе ви треба и база на податоци со неколку табели натоварени со примерок на податоци за да можете да вежбате користење на функции. Ве охрабруваме да го поминете следниов дел Поврзување со MySQL и Поставување примерок на база на податоци за детали за поврзување со MySQL сервер и креирање на базата за тестирање што се користи во примерите низ овој водич.

Поврзување со MySQL и поставување на база на податоци за примерок

Во овој дел, ќе се поврзете со MySQL сервер и ќе креирате примерок на база на податоци за да можете да ги следите примерите во следните делови.

За овој водич, ќе користите имагинарна колекција на колекционерски предмети. Ќе складирате детали за тековните колекционерски предмети, ќе ја одржувате нивната вкупна вредност лесно достапна и ќе се погрижите отстранувањето на колекционерскиот материјал секогаш да остави трага.

Ако вашиот систем за база на податоци SQL работи на оддалечен сервер, SSH во вашиот сервер од вашата локална машина:

  1. ssh sammy@your_server_ip

Потоа отворете го потсетникот за серверот MySQL, заменувајќи го sammy со името на вашата корисничка сметка на MySQL:

  1. mysql -u sammy -p

Создадете база на податоци со име колекционерски предмети:

  1. CREATE DATABASE collectibles;

Ако базата на податоци е успешно креирана, ќе добиете излез вака:

Output
Query OK, 1 row affected (0.01 sec)

За да ја изберете базата на податоци collectibles, извршете ја следнава изјава USE:

  1. USE collectibles;

Ќе го добиете следниот излез:

Output
Database changed

По изборот на базата на податоци, можете да креирате примероци табели во неа. Табелата колекционерски предмети ќе содржи поедноставени податоци за колекционерските предмети во базата на податоци. Ќе ги содржи следните колони:

  • име: оваа колона го содржи името за секој колекционер, изразено со користење на типот на податоци varchar со максимум 50 знаци. li>
  • вредност: оваа колона ја складира пазарната вредност на колекционерскиот материјал користејќи го типот на податоци decimal со максимум 5 вредности пред децималната точка и 2 вредности по него.

Направете примерок од табелата со следнава команда:

  1. CREATE TABLE collectibles (

  2.     name varchar(50),

  3.     value decimal(5, 2)

  4. );

Ако се отпечати следниот излез, табелата е креирана:

Output
Query OK, 0 rows affected (0.00 sec)

Следната табела ќе се вика collectibles_stats и ќе се користи за следење на акумулираната вредност на сите колекционерски предмети во колекцијата. Ќе содржи еден ред податоци со следните колони:

  • count: оваа колона го содржи бројот на сопствените колекционерски предмети, изразен со користење на типот на податоци int.
  • вредност: оваа колона ја складира акумулираната вредност на сите колекционерски предмети користејќи го типот на податоци decimal со максимум 5 вредности пред децималната точка и 2 вредности после него.

Направете примерок од табелата со следнава команда:

  1. CREATE TABLE collectibles_stats (

  2.     count int,

  3.     value decimal(5, 2)

  4. );

Ако се отпечати следниот излез, табелата е креирана:

Output
Query OK, 0 rows affected (0.00 sec)

Третата и последна табела ќе се вика collectibles_archive, која ќе ги следи сите колекционерски предмети што се отстранети од колекцијата за да се осигура дека никогаш нема да исчезнат. Ќе содржи податоци слични на табелата колекционерски предмети, надополнети со датумот на отстранување. Ги користи следните колони:

  • име: оваа колона го содржи името за секој отстранет колекционер, изразен со користење на типот на податоци varchar со максимум 50 знаци.< /li>
  • вредност: оваа колона ја складира пазарната вредност на колекционерскиот материјал во моментот на бришење користејќи го типот на податоци decimal со максимум 5 вредности пред децималната точка и вредностите 2 после неа.
  • removed_on: оваа колона ги зачувува датумот и времето на бришење за секој архивиран колекционерски материјал користејќи го типот на податоци timestamp со стандардна вредност NOW(), што значи тековниот датум секогаш кога ќе се вметне нов ред во оваа табела.

Направете примерок од табелата со следнава команда:

  1. CREATE TABLE collectibles_archive (

  2.     name varchar(50),

  3.     value decimal(5, 2),

  4.     removed_on timestamp DEFAULT CURRENT_TIMESTAMP

  5. );

Ако се отпечати следниот излез, табелата е креирана:

Output
Query OK, 0 rows affected (0.00 sec)

После тоа, вчитајте ја табелата collectibles_stats со почетната состојба за празната колекција на колекционерски предмети со извршување на следнава операција INSERT INTO:

  1. INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

Операцијата INSERT INTO ќе додаде еден ред во collectibles_stats со вредностите пресметани со помош на збирните функции за броење на сите редови во табелата колекционери и да се сумира вредноста на сите колекционерски предмети користејќи ја колоната вредност и функцијата SUM. Следниот излез покажува дека редот е додаден:

Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

Можете да го потврдите тоа со извршување на изјава SELECT на табелата:

  1. SELECT * FROM collectibles_stats;

Бидејќи сè уште нема колекционерски предмети во базата на податоци, почетниот број на ставки е 0, а акумулираната вредност вели NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Со тоа, подготвени сте да го следите остатокот од водичот и да започнете со користење на предизвикувачи во MySQL.

Разбирање на предизвикувачи

Активаторите се изјави дефинирани за одредена табела кои автоматски се извршуваат од базата на податоци секогаш кога ќе се појави одреден настан во таа табела. Активаторите може да се користат за да се гарантира дека некои дејства ќе се изведуваат доследно секогаш кога одредена изјава ќе се изврши на табела, наместо корисниците на базата на податоци да треба да запомнат да ги извршат рачно.

Секој активирач поврзан со табела е идентификуван со име дефинирано од корисникот и пар услови кои му укажуваат на моторот на базата на податоци кога да го изврши активирањето. Тие можат да се групираат во две посебни класи:

  • Настан во базата на податоци: активирањето може да се изврши кога се извршуваат изјавите INSERT, UPDATE или DELETE маса.
  • Време на настанот: Дополнително, активирачите може да се извршат ПРЕД или ПОСЛЕ дотичната изјава.

Комбинирањето на двете групи на услови дава вкупно шест посебни можности за активирање кои се извршуваат автоматски секој пат кога ќе се исполни заедничкиот услов. Предизвикувачите што се случуваат пред да се изврши исказот што го исполнува условот се ПРЕД Вметнете, ПРЕД АЖУРИРАЊЕ и ПРЕД БРИШЕЊЕ. Тие може да се користат за манипулација и валидација на податоците пред да се вметнат или ажурираат во табелата или да се зачуваат деталите од избришаниот ред за ревизија или архивски цели.

Предизвикувачите што се случуваат по извршувањето на исказот што го исполнува условот се По Вметнување, По АЖУРИРАЊЕ и ПО ИЗБРИШЕЊЕ. Тие може да се користат за ажурирање на сумираните вредности во посебна табела врз основа на конечната состојба на базата на податоци по изјавата.

За извршување дејства како што се потврдување и манипулирање со влезните податоци или архивирање на избришаниот ред, базата на податоци овозможува пристап до вредностите на податоците од внатре во активирачите. За предизвикувачите INSERT, може да се користат само нововнесените податоци. За предизвикувачите UPDATE, може да се пристапи и до оригиналните и до ажурираните податоци. Конечно, со предизвикувачите DELETE, достапни се само оригиналните податоци за редот (бидејќи нема нови податоци за повикување).

Податоците за употреба во телото на активирањето се изложени под записот OLD за податоците моментално во базата на податоци и записот NEW за податоците што ќе ги зачува барањето. Може да се повикате на поединечни колони користејќи ги синтаксата OLD.column_name и NEW.column_name.

Следниот пример ја прикажува општата синтакса на изјавата SQL што се користи за создавање на нов активирач:

  1. CREATE TRIGGER trigger_name trigger_condition

  2. ON table_name

  3. FOR EACH ROW

  4. trigger_actions;

Ајде да ја расчлениме синтаксата на помали делови:

  • CREATE TRIGGER е името на изјавата SQL што се користи за создавање на нов активирач во базата на податоци.
  • trigger_name е име на активирањето дефинирано од корисникот, што се користи за да се опише неговата улога, слично на тоа како имињата на табелите и имињата на колоните се користат за да се опише нивното значење.< /li>
  • ВКЛУЧЕНО име_табела и кажува на базата на податоци дека активирањето треба да ги следи настаните што се случуваат на табелата име_табела.
  • trigger_condition е еден од шесте можни избори што одредуваат кога треба да работи активирањето, на пример, ПРЕД Вметнете.
  • ЗА СЕКОЈ РЕД ѝ кажува на базата на податоци дека активирањето треба да се активира за секој ред погоден од настанот за активирање. Некои бази на податоци поддржуваат дополнителни шеми на извршување освен ЗА СЕКОЈ РЕД; сепак, во случајот на MySQL, извршувањето на изјавите од телото на активирањето за секој ред погоден од изјавата што го предизвикала активирањето е единствената опција.
  • trigger_actions е телото на активирањето и дефинира што се случува кога активирањето ќе се изврши. Тоа е обично една валидна SQL изјава. Можно е да се вклучат повеќе изјави во телото на активирањето за да се извршат сложени операции со податоци користејќи ги клучните зборови BEGIN и END за да се приложи списокот со искази во блок. Сепак, ова е надвор од опсегот на ова упатство. Проверете ја официјалната документација за предизвикувачи за да дознаете повеќе за синтаксата што се користи за дефинирање на предизвикувачите.

Во следниот дел, ќе креирате предизвикувачи кои манипулираат со податоци пред операциите INSERT и UPDATE.

Манипулирање со податоци со активирања ПРЕД Вметнување и ПРЕД АЖУРИРАЊЕ

Во овој дел, ќе користите предизвикувачи за манипулирање со податоци пред да се извршат изјавите INSERT и UPDATE.

Во овој пример, ќе користите предизвикувачи за да се осигурате дека сите колекционерски предмети во базата на податоци користат големи имиња за конзистентност. Без користење на предизвикувачи, ќе мора да запомните да користите големи колекционерски имиња за секоја изјава INSERT и UPDATE. Ако заборавите, базата на податоци ќе ги зачува податоците како што се, што ќе доведе до можни грешки во базата на податоци.

Ќе започнете со вметнување примерен колекционерски предмет наречен модел на вселенски брод во вредност од 12,50 $. Името на ставката ќе биде напишано со мали букви за да се илустрира проблемот. Извршете ја следнава изјава:

  1. INSERT INTO collectibles VALUES ('spaceship model', 12.50);

Следната порака потврдува дека ставката е додадена:

Output
Query OK, 1 row affected (0.009 sec)

Можете да потврдите дека редот е вметнат со извршување на барањето SELECT:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

Колекционерскиот предмет е зачуван како што е, со името напишано само со мали букви.

За да се осигурате дека сите идни колекционерски предмети секогаш ќе бидат напишани со големи букви, ќе создадете активирач ПРЕД Вметнете. Користењето на активирач што се извршува пред да се изврши исказот за активирање ви овозможува да манипулирате со податоците што ќе бидат предадени во базата на податоци пред да се случи.

Извршете ја следнава изјава:

  1. CREATE TRIGGER uppercase_before_insert BEFORE INSERT

  2. ON collectibles

  3. FOR EACH ROW

  4. SET NEW.name = UPPER(NEW.name);

Оваа команда создава активирач со име uppercase_before_insert што ќе се изврши ПРЕД сите INSERT изјави на табелата со име колекционери.

Изјавата во активирањето SET NEW.name=UPPER(NEW.name) ќе се изврши за секој вметнат ред. Командата SET SQL ја доделува вредноста од десната страна на левата страна. Во овој случај, NEW.name ја претставува вредноста на колоната име што ќе ја зачува изјавата за вметнување. Со примена на функцијата UPPER на колекционерското име и доделување назад на вредноста на колоната, ја претворате големината на буквата од вредноста што ќе биде зачувана во базата на податоци.

Забелешка: кога ја извршувате командата CREATE TRIGGER, може да наидете на порака за грешка слична на ERROR 1419 (HY000): немате SUPER привилегија и бинарни пријавувањето е овозможено (*можеби* сакате да ја користите помалку безбедната променлива log_bin_trust_function_creators).

Почнувајќи со MySQL 8, моторот на базата на податоци MySQL има стандардно овозможено бинарно евидентирање, освен ако конфигурацијата за локална инсталација не го надмине тоа. Бинарниот дневник ги следи сите SQL изјави кои ја менуваат содржината на базата на податоци во форма на зачувани настани кои ги опишуваат модификациите. Овие дневници се користат при репликација на базата на податоци за да се држат синхронизирани копии на базата на податоци и за време на обновувањето на податоците во моментот.

Со овозможено бинарно евидентирање, MySQL не дозволува создавање на предизвикувачи и складирани процедури како мерка на претпазливост за да се гарантира безбедноста и интегритетот на податоците во реплицираните средини. Разбирањето како предизвикувачите и складираните процедури можат да влијаат на репликацијата е надвор од опсегот на ова упатство.

Меѓутоа, во локално опкружување и за цели на учење, можете безбедно да го отфрлите начинот на кој MySQL се чува од создавање предизвикувачи. Откриената поставка не е опстојувана и ќе се врати на првобитната вредност кога ќе се рестартира серверот MySQL.

За да ја отфрлите стандардната поставка за бинарно најавување, најавете се на MySQL како root и извршете ја следнава команда:

  1. SET GLOBAL log_bin_trust_function_creators = 1;

Поставката log_bin_trust_function_creators контролира дали на корисниците што создаваат предизвикувачи и складирани функции може да им се верува дека не создаваат предизвикувачи што предизвикуваат небезбедни настани да се запишуваат во бинарниот дневник. Стандардно, вредноста на поставката е 0, дозволувајќи им на само суперкорисниците да создаваат предизвикувачи во средина со овозможено бинарно најавување. Со промена на вредноста во 1, на секој корисник што издава изјави CREATE TRIGGER ќе му се верува да ги разбере импликациите.

По ажурирањето на поставката, одјавете се како root, најавете се повторно како корисник и повторно активирајте ја изјавата CREATE TRIGGER.

За да дознаете повеќе за бинарното најавување и репликација во MySQL и како тоа се поврзува со предизвикувачите, ве охрабруваме да се повикате на официјалната документација на MySQL: Како да поставите репликација во MySQL.

Пред да користите предизвикувачи во производствена средина со воспоставена репликација или строги барања за обновување точка-во-време, проверете дали сте го измериле нивното влијание врз конзистентноста на бинарниот дневник.

Забелешка: во зависност од дозволите на корисникот MySQL, може да добиете грешка при извршувањето на командата CREATE TRIGGER: ERROR 1142 (42000): командата TRIGGER е одбиена на корисникот @host за колекционерски предмети за маса. За да му дадете дозволи на TRIGGER на вашиот корисник, најавете се на MySQL како root и извршете ги следните команди, заменувајќи ги корисничкото име и домаќинот MySQL по потреба:

  1. GRANT TRIGGER on *.* TO 'sammy'@'localhost';

  2. FLUSH PRIVILEGES;

По ажурирањето на корисничките дозволи, одјавете се како root, најавете се повторно како корисник и повторно извршете ја изјавата CREATE TRIGGER.

MySQL ќе ја испечати следнава порака за да потврди дека активирањето е успешно креирано:

Output
Query OK, 1 row affected (0.009 sec)

Сега обидете се да вметнете нов колекционер, повторно користејќи мал аргумент на барањето INSERT:

  1. INSERT INTO collectibles VALUES ('aircraft model', 10.00);

И уште еднаш, проверете ги добиените редови во табелата колекционерски предмети:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Меѓутоа, овој пат, новиот запис вели МОДЕЛ НА АВИОН со сите букви со големи букви - различни од записот што се обидовте да го вметнете. Активирањето работи во заднина и ја конвертира буквата пред редот да биде зачуван во базата на податоци.

Сите нови редови сега се чуваат со активирањето за да се осигура дека имињата ќе бидат зачувани со големи букви. Сепак, сè уште е можно да се зачуваат неограничени податоци со користење на изјавите UPDATE. За да ги заштитите изјавите UPDATE со истиот ефект, креирајте друг активирач:

  1. CREATE TRIGGER uppercase_before_update BEFORE UPDATE

  2. ON collectibles

  3. FOR EACH ROW

  4. SET NEW.name = UPPER(NEW.name);

Разликата помеѓу двата предизвикувачи е во критериумите за активирање. Овој пат, тоа е ПРЕД АЖУРИРАЊЕ, што значи дека активирањето ќе се извршува секој пат кога ќе се издава изјава UPDATE на табелата - што влијае на постоечките редови при секое ажурирање , покрај новите редови покриени со претходниот активирач.

MySQL ќе издаде потврда дека активирањето е успешно креирано:

Output
Query OK, 0 row affected (0.009 sec)

За да го потврдите однесувањето на новиот активирач, обидете се да ја ажурирате вредноста на цената за моделот на вселенски брод:

  1. UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

Клаузулата WHERE го филтрира редот што треба да се ажурира по име, а клаузулата SET ја менува вредноста во 15.00.

Ќе го добиете следниов излез, потврдувајќи дека изјавата сменила еден ред:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Проверете ги добиените редови во табелата колекционерски предмети:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Сега, покрај ажурирањето на цената на 15.00 со извршената изјава, името сега вели МОДЕЛ НА ВСЕЛЕН БРОД. Кога ја извршивте изјавата UPDATE, активирањето беше извршено, што влијаеше на вредностите на ажурираниот ред. Колоната за име беше претворена во големи букви пред да се зачува.

Во овој дел, создадовте два предизвикувачи кои работат пред барањата INSERT и пред UPDATE за да ги усогласат податоците пред да ги зачувате во базата на податоци. Во следниот дел, ќе користите активирања ПРЕД БРИШИЊЕ за да ги копирате избришаните редови во посебна табела за архивирање.

Користење на активирачите ПРЕД БРИШЕЊЕ за извршување дејства пред бришење редови

Дури и ако повеќе не поседувате ставка, можеби ќе сакате да оставите запис за бришењето во посебна табела. На почетокот на ова упатство, креиравте втора табела наречена collectibles_archive за да ги следите сите колекционерски предмети што се отстранети од колекцијата. Во овој дел, ќе ги архивирате избришаните записи со активирач што ќе се изврши пред изјавите DELETE.

Проверете дали табелата со архиви е целосно празна со извршување на следнава изјава:

  1. SELECT * FROM collectibles_archive;

Следниот излез ќе се отпечати на екранот, потврдувајќи дека табелата collectibles_archive е празна:

Output
Empty set (0.000 sec)

Сега, ако поставите барање ИЗБРИШИ против табелата колекционери, секој ред од табелата може да се избрише без трага.

За да го поправите тоа, ќе создадете активирач што ќе се изврши пред сите барања за DELETE на табелата колекционерски предмети. Целта на овој активирач е да се зачува копија од избришаниот објект во табелата за архива пред да се случи бришењето.

Извршете ја следнава команда:

  1. CREATE TRIGGER archive_before_delete BEFORE DELETE

  2. ON collectibles

  3. FOR EACH ROW

  4. INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

Активирањето е наречено archive_before_delete и се случува BEFORE какви било прашања DELETE на табелата колекционерски предмети . За секој ред што ќе се избрише, ќе се изврши изјавата INSERT. За возврат, изјавата INSERT вметнува нов ред во табелата collectibles_archive со вредности на податоци земени од записот OLD, кој е наведен за бришење: OLD.name станува колона име и OLD.value станува колона вредност.

Базата на податоци ќе го потврди создавањето на активирањето:

Output
Query OK, 0 row affected (0.009 sec)

Со активирањето на место, обидете се да избришете колекционерски материјал од главната табела за колекционерски предмети:

  1. DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

Излезот потврдува дека барањето се изврши успешно:

Output
Query OK, 1 row affected (0.004 sec)

Сега, наведете ги сите колекционерски предмети:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

Сега останува само МОДЕЛ НА АВИОН; МОДЕЛ НА SPACESHIP е избришан и повеќе не е во табелата. Меѓутоа, со претходно креираниот активирач, ова бришење треба да се регистрира во табелата collectibles_archive. Да го провериме тоа.

Извршете друго барање:

  1. SELECT * FROM collectibles_archive;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

Бришењето беше автоматски забележано во таа табела со активирањето. Колоните име и вредност се пополнети со податоци од редот што беше избришан. Третата колона, removed_on, не е експлицитно поставена преку дефинираниот активирач, така што ја зема стандардната вредност одлучена при креирањето на табелата: датумот на создавање на кој било нов ред. Поради тоа, секој запис додаден со помош на активирањето секогаш ќе биде означен со датумот на бришење.

Со поставеното активирање, сега можете да бидете сигурни дека сите барања за DELETE ќе резултираат со запис во дневникот во collectibles_archive, оставајќи ги зад себе информациите за претходно поседуваните колекционерски предмети.

Во следниот дел, ќе ги користите активирачите извршени по исказите за активирање за да ја ажурирате резимената табела со збирни вредности врз основа на сите колекционерски предмети.

Користење AFTER INSERT, AFTER APDATE и AFTER DELETE предизвикувачи за извршување дејства по манипулација со податоци

Во двата претходни делови, користевте предизвикувачи извршени пред главните изјави за да извршите операции врз основа на оригиналните податоци пред да ја ажурирате базата на податоци. Во овој дел, ќе ја ажурирате резимената табела со секогаш ажуриран број и акумулираната вредност на сите колекционерски предмети со помош на активирачи кои се извршуваат по планираните изјави. На овој начин, ќе бидете сигурни дека податоците од збирната табела ја земаат предвид моменталната состојба на базата на податоци.

Започнете со испитување на табелата collectibles_stats:

  1. SELECT * FROM collectibles_stats;

Бидејќи сè уште не сте додале информации на оваа табела, бројот на колекционерски предмети во сопственост е 0, а со тоа, акумулираната вредност е NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Бидејќи нема активирања за оваа табела, претходно објавените барања за вметнување и ажурирање колекционерски предмети не влијаеја на оваа табела.

Целта е да се постават вредностите во еден ред во табелата collectibles_stats за да се презентираат ажурирани информации за бројот и вкупната вредност на колекционерските предмети. Сакате да се осигурате дека содржината на табелата се ажурира по секоја операција INSERT, UPDATE или DELETE.

Можете да го направите тоа со создавање на три посебни предизвикувачи, сите извршени по соодветното барање. Прво, креирајте го активирачот AFTER INSERT:

  1. CREATE TRIGGER stats_after_insert AFTER INSERT

  2. ON collectibles

  3. FOR EACH ROW

  4. UPDATE collectibles_stats

  5. SET count = (

  6.     SELECT COUNT(name) FROM collectibles

  7. ), value = (

  8.     SELECT SUM(value) FROM collectibles

  9. );

Активирањето е наречено stats_after_insert и ќе го извршува AFTER секое барање INSERT во колекционерите табела, извршувајќи ја изјавата UPDATE во телото на активирањето. Барањето UPDATE влијае на collectibles_stats и ги поставува колоните count и value на вредностите вратени од вгнездените барања:

  • SELECT COUNT(име) ОД колекционерски предмети ќе го добие бројот на колекционерски предмети.
  • SELECT SUM(вредност) ОД колекционерски предмети ќе ја добие вкупната вредност на сите колекционерски предмети.

Базата на податоци ќе го потврди создавањето на активирањето:

Output
Query OK, 0 row affected (0.009 sec)

Сега, обидете се повторно да го вметнете претходно избришаниот модел на вселенски брод во табелата со колекционерски предмети за да проверите дали резимената табела ќе биде правилно ажурирана:

  1. INSERT INTO collectibles VALUES ('spaceship model', 15.00);

Базата на податоци ќе ја испечати следната успешна порака:

Output
Query OK, 1 row affected (0.009 sec)

Можете да ги наведете сите колекционерски предмети во сопственост со извршување:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Има два колекционерски предмети во вкупна вредност од 25.00. За да ја испитате табелата за резиме по ново внесената ставка, извршете го следново барање:

  1. SELECT * FROM collectibles_stats;

Овој пат, резимената табела ќе го наведе бројот на сите сопствени колекционерски предмети како 2 и акумулираната вредност како 25.00, што се совпаѓа со претходниот излез:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

Активирањето stats_after_insert работи по барањето INSERT и ја ажурира табелата collectibles_stats со тековните податоци (број и value) за колекцијата. Се собираат статистики за целата содржина на збирката, а не само за последната влошка. Бидејќи колекцијата сега содржи две ставки (модели на авиони и вселенски бродови), резимената табела наведува две ставки и нивната сумирана вредност. Во овој момент, додавањето на која било нова колекционерска ставка во табелата со колекционерски предмети ќе ја ажурира резимената табела со точните вредности.

Сепак, ажурирањето на постоечките ставки или бришењето на колекционерските предмети воопшто нема да влијае на резимето. За да ја пополните таа празнина, ќе создадете два дополнителни предизвикувачи, кои вршат идентични операции, но активирани од различни настани:

  1. CREATE TRIGGER stats_after_update AFTER UPDATE

  2. ON collectibles

  3. FOR EACH ROW

  4. UPDATE collectibles_stats

  5. SET count = (

  6.     SELECT COUNT(name) FROM collectibles

  7. ), value = (

  8.     SELECT SUM(value) FROM collectibles

  9. );

  10. 

  11. CREATE TRIGGER stats_after_delete AFTER DELETE

  12. ON collectibles

  13. FOR EACH ROW

  14. UPDATE collectibles_stats

  15. SET count = (

  16.     SELECT COUNT(name) FROM collectibles

  17. ), value = (

  18.     SELECT SUM(value) FROM collectibles

  19. );

Сега создадовте два нови предизвикувачи: stats_after_update и stats_after_delete. Двата предизвикувачи ќе работат на табелата collectible_stats секогаш кога ќе извршите изјава UPDATE или DELETE на табелата collectibles.

Успешното создавање на тие предизвикувачи ќе го отпечати следниот излез:

Output
Query OK, 0 row affected (0.009 sec)

Сега, ажурирајте ја вредноста на цената за еден од колекционерските предмети:

  1. UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

Клаузулата WHERE го филтрира редот што треба да се ажурира по име, а клаузулата SET ја менува вредноста во 25.00.

Излезот потврдува дека изјавата сменила еден ред:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Уште еднаш, проверете ја содржината на табелата за резиме по ажурирањето:

  1. SELECT * FROM collectibles_stats;

На вредноста сега е наведено 40.00, што е точната вредност по ажурирањето:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

Последниот чекор е да се потврди дека табелата за резиме правилно ќе го одрази бришењето на колекционерски материјал. Обидете се да го избришете моделот на авионот со следнава изјава:

  1. DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

Следниот излез потврдува дека барањето се изврши успешно:

Output
Query OK, 1 row affected (0.004 sec)

Сега, наведете ги сите колекционерски предмети:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

Сега останува само SPACESHIP MODEL. Следно, проверете ги вредностите во резимената табела:

  1. SELECT * FROM collectibles_stats;

Ќе се отпечати следниов излез:

Output
+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

Колоната count сега покажува дека само еден колекционер во главната табела. Вкупната вредност е 15,00, што одговара на вредноста на SPACESHIP MODEL.

Овие три предизвикувачи работат заедно по барањата INSERT, UPDATE и DELETE за да се задржи резимената табела синхронизирана со комплетната листа на колекционерски предмети.

Во следниот дел, ќе научите како да манипулирате со постоечките предизвикувачи на базата на податоци.

Наведување и бришење предизвикувачи

Во претходните делови, креиравте нови предизвикувачи. Бидејќи предизвикувачите се именувани објекти дефинирани во базата на податоци, исто како и табелите, можете исто така да ги наведете и да манипулирате со нив кога е потребно.

За да ги наведете сите предизвикувачи, извршете ја изјавата SHOW TRIGGERS:

  1. SHOW TRIGGERS;

Излезот ќе ги вклучи сите предизвикувачи, вклучувајќи ги и нивните имиња, активирачкиот настан со време (ПРЕД или AFTER извршувањето на изјавата), како и искази кои се дел од телото на активирањето и други опширни детали за дефиницијата на активирањето:

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

За да ги избришете постоечките предизвикувачи, можете да користите DROP TRIGGER SQL искази. Можеби веќе не сакате да наметнувате големи букви за колекционерските имиња, па затоа големи_пред_внеси и големи_пред_ажурирање веќе не се потребни. Извршете ги следните команди за да ги отстраните овие два предизвикувачи:

  1. DROP TRIGGER uppercase_before_insert;

  2. DROP TRIGGER uppercase_before_update;

За двете команди, MySQL ќе одговори со успешна порака:

Output
Query OK, 0 rows affected (0.004 sec)

Сега, откако ги нема двата предизвикувачи, ајде да додадеме нов колекционер со мали букви:

  1. INSERT INTO collectibles VALUES ('ship model', 10.00);

Базата на податоци ќе го потврди вметнувањето:

Output
Query OK, 1 row affected (0.009 sec)

Можете да потврдите дека редот е вметнат со извршување на барањето SELECT:

  1. SELECT * FROM collectibles;

Следниот излез ќе се отпечати на екранот:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Новододадениот колекционерски материјал е со мали букви. Бидејќи името е непроменето од оригиналниот излез, потврдивте дека активирањето што претходно ја конвертира буквата веќе не се користи.

Сега знаете како да ги наведете и бришете предизвикувачите по име.

Заклучок

Следејќи го ова упатство, научивте што се предизвикувачите на SQL и како да ги користите во MySQL за манипулирање со податоци пред барањата INSERT и UPDATE. Научивте како да го користите активирањето ПРЕД ИЗБРИШЕЊЕ за да го архивирате избришаниот ред во посебна табела, како и да користите предизвикувачи на изјавата AFTER за да ги одржувате резимеата постојано ажурирани.

Можете да користите функции за да префрлите дел од манипулацијата и валидацијата на податоците во моторот на базата на податоци, обезбедувајќи интегритет на податоците или криејќи некои од однесувањата на базата на податоци од дневниот корисник на базата на податоци. Овој туторијал ги опфати само основите за користење на тригери за таа цел. Можете да изградите сложени предизвикувачи што се состојат од повеќе изјави и да користите условна логика за да извршите дејства уште погрануларно. За да дознаете повеќе за тоа, погледнете ја документацијата MySQL за предизвикувачите.

Доколку сакате да дознаете повеќе за различните концепти околу јазикот SQL и работата со него, ве охрабруваме да ги погледнете другите водичи во серијата Како да се користи SQL.