В Части 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 г.