Язык SQL. Устранение конфликтов в базах данных при помощи SQL функции RAIS и триггеров.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zametki-o-mysql/tema-13-triggery-v-sql-na-primere-bazy-dannyx-sqlite.html
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

— Функция RAISE () и SQL триггеры в базах данных SQLite

— к сожалению, ни одна в мире СУБД не знает, например, что Хемингуэй написал «Старик и море», а Исаак Ньютон – это физик, поэтому когда мы наполняем наши таблицы данными, могут возникать не очевидные ошибки с точки зрения логики реляционных баз данных, от которых не сможет защитить ни одна нормальная форма.
— Например, когда мы говорили про внешние ключи, мы реализовывали связь один ко многим между таблицами, для этого мы создавали результирующую таблицу и я демонстрировал пример неудачно добавления данных, при котором получалось, что Джек Лондон написал «Войну и мир». Такие ошибки могут происходить довольно часто и их можно назвать конфликтами в базе данных.
— Конфликт в базе данных не стоит путать с аномалией, потому что с точки зрения логики СУБД ничего криминального не происходит, а вот с точки зрения человека, знающего предметную область происходит существенная ошибка, которая может ввести человека в ступор.
— Избавиться от подобных конфликтов мы можем при помощи триггеров и функции RAISE ()

/*
Отметим, что RAISE () – это специальная функция, которая используется только вместе с триггерами, ее синтаксис вы найдете на рисунке ниже.

Функция RAISE может принимать два аргумента. Первый аргумент описывает действие при возникновении конфликта:
Значение IGNORE. Говорит SQLite3 о том, чтобы она игнорировала строку породившую конфликт и продолжала выполнение последующих операций.
Значение ROLLBACK. ROLLBACK говорит о том, что SQLite должна откатить все операции к исходному состоянию при возникновении конфликта. При этом пользователь может изменить и повторить запрос. Не путайте с командой ROLLBACK, которая откатывая транзакцию
Значение ABORT. Данное значение похоже на ROLLBACK, но разница в том, что оно отменяет не все выполненные ранее SQL запросы, а только тот запрос, при котором возникла конфликтная ситуация.
Значение FAIL. Данное значение говорит СУБД о том, что нужно прервать выполнение текущей операции и сохранить результаты успешных операций, при этом операции, следующие за конфликтной, выполнены не будут.
*/

— Создадим таблицы и наполним их данными
PRAGMA foreign_keys=on;
CREATE TABLE books(
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
count_page INTEGER NOT NULL),
price REAL
);

CREATE TABLE auth(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);

CREATE TABLE auth_book (
auth_id INTEGER NOT NULL,
books_id INTEGER NOT NULL,
FOREIGN KEY (auth_id) REFERENCES auth(id)
FOREIGN KEY (books_id) REFERENCES books(id)
);

INSERT INTO books (id, title, count_page, price)
VALUES (1, ‘Белый клык’, 287, 300.00);

INSERT INTO books (id, title, count_page, price)
VALUES (2, ‘Война и мир’, 806, 780.00);

INSERT INTO books (id, title, count_page, price)
VALUES (3, ’12 стульев’, 516, 480.00);

— Затем добавим несколько авторов
INSERT INTO auth (id, name, age)
VALUES (1, ‘Джек Лондон’, 40);

INSERT INTO auth (id, name, age)
VALUES (2, ‘Лев Толстой’, 82);

INSERT INTO auth (id, name, age)
VALUES (3, ‘Илья Ильф’, 39);

INSERT INTO auth (id, name, age)
VALUES (4, ‘Евгений Петров’, 38);

— Мы не стали наполнять таблицу-справочник, которая реализует связь многие ко многим
— Давайте сперва напишем триггер, который будет проверять добавляемые значения

1 2

CREATE TRIGGER books_result BEFORE INSERT
ON auth_book
BEGIN
SELECT RAISE(FAIL, ‘Произошла ошибка, вы неправильно связали автора и книгу’) FROM auth_book
WHERE (NEW.auth_id = 1 AND books_id = 2) OR (NEW.auth_id = 1 AND NEW.books_id = 3) OR
(NEW.auth_id = 2 AND NEW.books_id = 3) OR (NEW.auth_id = 2 AND NEW.books_id = 3) OR
(NEW.auth_id = 3 AND NEW.books_id = 2) OR (NEW.auth_id = 3 AND NEW.books_id = 1) OR
(NEW.auth_id = 4 AND NEW.books_id = 2) OR (NEW.auth_id = 4 AND NEW.books_id = 1);
END;

— Проверим работу триггера
INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)
VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)
VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (4, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 2);

SELECT * FROM auth_book;