- Разработка
- Бизнес
- ИТ и ПО
- Личностный рост
- Дизайн
- Маркетинг
- Красота
- Фотография и видео
- Здоровье и спорт
- Музыка
- Образование
- Подготовка к экзаменам
- Языки
- Работа на компьютере
- Строительство и ремонт
- Кулинария
- Сельское хозяйство
- Фриланс
- ПДД и вождение
- Игры
- Школьное образование
- Профессиональная ориентация
- Шитье и кожевенное дело
- Рукоделие
- Каменные и столярные работы
- Собеседование
- Рыбалка и охота
- Страны
Язык SQL. Устранение конфликтов в базах данных при помощи SQL функции RAIS и триггеров.
Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: 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;