В Части 1 этой серии, состоящей из двух частей, обсуждались требования к реализации проверки целостности данных с использованием файла TRIGGER. После успеха этого TRIGGER, этот второй пост в блоге углубится в его работу, чтобы понять, как выполняются проверки, а также сам TRIGGER.

Примечание. Все данные, имена или имена, найденные в базе данных, представленной в этом посте, используются строго для практики, обучения, обучения и тестирования. Он ни в коем случае не отображает фактические данные, принадлежащие или используемые какой-либо стороной или организацией.

Для этих упражнений я буду использовать Xubuntu Linux 16.04.3 LTS (Xenial Xerus) и MySQL 5.7.21.

Гайки и болты

Что такое TRIGGER?

TRIGGER — это объект базы данных, представляющий собой особый вид процедуры, вызываемой неявно из-за некоторого события в базе данных.

Для начала давайте вернемся к collect_to_stage TRIGGER.

DELIMITER $$
 CREATE TRIGGER collect_to_stage BEFORE INSERT
 ON asset_staging
 FOR EACH ROW
 BEGIN
 IF NEW.kind = 0 THEN
 SET NEW.kind = ‘other’;
 INSERT INTO flagged_asset(f_pipe_name, f_pipe_jt_num, f_pipe_heat,
 f_pipe_length, f_defect, f_message, import_date)
 VALUES(NEW.pipe_name, NEW.pipe_jt_num, NEW.pipe_heat, NEW.pipe_length,
 ‘Unknown Type’, CONCAT(NEW.pipe_name, ‘ was not in the acceptable list.’), NOW());
 END IF;
 IF NEW.pipe_length < = 0 THEN
 INSERT INTO flagged_asset(f_pipe_name, f_pipe_jt_num, f_pipe_heat,
 f_pipe_length, f_defect, f_message, import_date)
 VALUES(NEW.pipe_name, NEW.pipe_jt_num, NEW.pipe_heat, NEW.pipe_length,
 ‘Questionable Length’, CONCAT(NEW.pipe_name, ‘ needs the length verified for accuracy.’), NOW());
 END IF;
 END $$
 DELIMITER ;

Как этот TRIGGER выполняет требуемую работу?
Давайте разберемся построчно.

  • Строка 1: DELIMITER $$. В строках 1 и 20 используется одно и то же ключевое слово, но его значение различно. Общность заключается в установке DELIMITER. Разница в используемых символах. Зачем переопределять его для начала?
    По умолчанию оператор DELIMITER, распознаваемый программой командной строки mysql, представляет собой точку с запятой (;). Чтобы использовать ее (точку с запятой) в нескольких операторах, требуется альтернативный символ.
  • Строка 2: CREATE TRIGGER collect_to_stage BEFORE INSERT — Строка 2 определяет время TRIGGER, указывая на то, что будет запущена BEFORE операция INSERT.
  • Строка 3: ON asset_staging — целевая таблица.
  • Строка 4: FOR EACH ROW — указывает на срабатывание TRIGGER, происходящее для каждой строки, затронутой условием события. В данном случае INSERT.
  • Строка 5: BEGIN — используется для начала раздела составных операторов.
  • Строка 6: IF NEW.kind = 0 THEN. Строка 6 необходима для проверки целостности столбца kind в условном блоке IF. Тип данных ENUM представляет собой строковый объект, состоящий из разрешенного списка значений, назначенных при создании. Каждое значение имеет индекс. Индекс для недопустимого значения равен 0 (ноль). С помощью этой информации мы можем проверить значение столбца NEW.kind. Если значение равно равно 0, то для этого столбца сохранено недопустимое значение.
    В зависимости от типа события TRIGGER, ключевых слов NEW и OLD включите доступ к значениям столбца строки. Стоит отметить, что операции INSERT дают только значение NEW.column.
  • Строка 7: SET NEW.kind = 'other';. Здесь я устанавливаю для неприемлемого столбца kind значение другое до тех пор, пока его нельзя будет изучить, определить правильный тип актива и исправить. Оператор SET намеренно расположен в этом блоке IF. Находя его здесь, установка согласованного «другого» значения происходит только в том случае, если было импортировано недопустимое значение. Поэтому мы по-прежнему сохраняем допустимые значения для тех записей, в которых они есть.
  • Строки 8–11:
INSERT INTO flagged_asset(f_pipe_name, f_pipe_jt_num, f_pipe_heat, f_pipe_length, f_defect, f_message)
 VALUES(NEW.pipe_name, NEW.pipe_jt_num, NEW.pipe_heat, NEW.pipe_length,
 ‘Unknown Type’, CONCAT(NEW.pipe_name, ‘ was not in the acceptable list.’));
  • Этот оператор INSERT сохраняет запись в таблице flagged_asset из-за неудачной проверки столбца.
  • Строка 12: END IF; — конец этого условного блока.
  • Строка 13: IF NEW.pipe_length <= 0 THEN — начало этой второй проверки внутри условного блока IF. Если значение pipe_length равно 0 (нулю) или меньше, оно помечается. Достаточно просто.
  • Строка 14–17:
INSERT INTO flagged_asset(f_pipe_name, f_pipe_jt_num, f_pipe_heat, f_pipe_length, f_defect, f_message)
 VALUES(NEW.pipe_name, NEW.pipe_jt_num, NEW.pipe_heat, NEW.pipe_length,
 ‘Questionable Length’, CONCAT(NEW.pipe_name, ‘ needs the length verified for accuracy.’));
  • Записи, не прошедшие проверку длины, вставляются в таблицу flagged_asset.
  • Строка 18: END IF; — завершает этот IF условный блок.
  • Строка 19: END $$ — завершение этой конструкции BEGIN...END и выполнение триггера с $$, который является разделителем. Сейчас.
  • Строка 20: DELIMITER ; — переустановка разделителя обратно на точку с запятой по умолчанию (;)

Обязательно посетите страницу Лучшее из на моем сайте Digital Owl’s Prose, где собрана коллекция моих лучших постов в блоге.

Последние мысли

От обеспечения соблюдения бизнес-правил до обеспечения целостности ваших данных триггеры имеют свое место в базах данных. Часто они используются для ведения журналов событий.
Хотя это и не обсуждается в этой серии статей, MySQL позволяет таблице иметь несколько триггеров с одинаковым временем и действием события, определяя порядок срабатывания с помощью PRECEDES или FOLLOWS, за которыми следует существующий TRIGGER.

Это обертка

Вау! Эта серия из двух частей о триггерах поразила меня! Я так многому научился с этой концепцией их использования, и я надеюсь, что вы тоже. Я хотел бы услышать любые отзывы о триггерах от тех, кто более опытен с ними.

Изучите официальное Онлайн-руководство по MySQL 5.7 для подробного обзора всех тем, затронутых в этом посте.

Призыв к действию!

Спасибо, что нашли время, чтобы прочитать этот пост. Я искренне надеюсь, что вы открыли для себя что-то интересное и поучительное. Пожалуйста, поделитесь своими выводами здесь с кем-то из ваших знакомых, кто также получит от этого такую ​​же пользу.

Я упоминал, как сильно я люблю чашку кофе?!?!

Джош Отвелл страстно желает учиться и расти как разработчик SQL и блоггер. Другие любимые занятия заставят его уткнуться носом в хорошую книгу, статью или командную строку Linux. Среди них он разделяет любовь к настольным ролевым играм, чтению фантастических романов и проведению времени с женой и двумя дочерьми.

Отказ от ответственности: примеры, представленные в этом посте, являются гипотетическими идеями о том, как достичь подобных результатов. Это не самое лучшее решение(я). Ваши конкретные цели и потребности могут отличаться. Используйте те методы, которые лучше всего подходят для ваших нужд и целей. Мнения мои собственные.

Первоначально опубликовано на сайте joshuaotwell.com 31 января 2018 г.