Триггеры SQLite

Введение в триггеры

Триггер — это управляемое событиями действие, которое запускается автоматически, когда указанная операция изменения (инструкции INSERT, UPDATE и DELETE) выполняется в указанной таблице. Триггеры полезны для таких задач, как соблюдение бизнес-правил, проверка входных данных и ведение контрольного журнала.

Оглавление

Использование триггеров

Преимущества использования триггеров в бизнесе

Создание триггеров SQLite

Пример базы данных, таблица, структура таблицы, записи таблицы

Триггер SQLite: пример ПОСЛЕ ВСТАВКИ

Триггер SQLite: пример ПЕРЕД ВСТАВКОЙ

Триггер SQLite: пример ПОСЛЕ ОБНОВЛЕНИЯ

Триггер SQLite: пример ПЕРЕД ОБНОВЛЕНИЕМ

Триггер SQLite: пример ПОСЛЕ УДАЛЕНИЯ

Триггер SQLite: пример ПЕРЕД УДАЛЕНИЕМ

SQLite Триггер: пример INSERT с использованием INSTEAD OF

Триггер SQLite: пример UPDATE с использованием INSTEAD OF

Триггер SQLite: пример DELETE с использованием INSTEAD OF

Удаление/удаление триггера SQLite

Использование для триггеров:

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

Преимущества использования триггеров в бизнесе:

Более быстрая разработка приложений. Поскольку база данных хранит триггеры, вам не нужно кодировать действия триггеров в каждом приложении базы данных.
Глобальное применение бизнес-правил. Определите триггер один раз, а затем повторно используйте его для любого приложения, использующего базу данных.
Упрощение обслуживания. Если бизнес-политика изменяется, вам нужно изменить только соответствующую программу триггера, а не каждую прикладную программу.
Повысьте производительность в среде клиент/сервер. Все правила выполняются на сервере до возврата результата.

Реализация триггеров SQL основана на стандарте SQL. Он поддерживает конструкции, общие для большинства языков программирования. Он поддерживает объявление локальных переменных, операторов для управления потоком процедуры, присвоение результатов выражения переменным и обработку ошибок.

SQLite: Create trigger

Триггер — это именованный объект базы данных, связанный с таблицей, и он активируется, когда для таблицы/представлений происходит определенное событие (например, вставка, обновление или удаление). Оператор CREATE TRIGGER создает новый триггер в SQLite. Оператор CREATE TRIGGER используется для добавления триггеров в схему базы данных. Триггеры — это операции с базой данных, которые автоматически выполняются при наступлении указанного события базы данных..

Вот синтаксис:

Синтаксис:

 CREATE [TEMP |  TEMPORARY] TRIGGER имя-триггера [BEFORE |  ПОСЛЕ] событие-базы-данных ВКЛЮЧЕНО [имя-базы-данных.] Имя-таблицы-триггер-действие-действие-триггер дополнительно определяется как: [ДЛЯ КАЖДОЙ СТРОКИ |  ДЛЯ КАЖДОГО ЗАЯВЛЕНИЯ] [WHEN expression] BEGINtrigger-step;  [trigger-step;] * END

Параметры:

Имя	Описание
триггер -name	Имя триггера. Триггер должен отличаться от имени любого другого триггера для той же таблицы. Имя не может быть дополнено схемой — триггер наследует схему своей таблицы.
BEFORE AFTER INSTEAD OF	Определяет, вызывается ли функция до или после , или вместо события. Триггер ограничения может быть указан только как AFTER.
database-event	Одно из INSERT, UPDATE, DELETE, которое будет активировать триггер.
table-name	Имя таблицы или представления, для которого предназначен триггер.
ДЛЯ КАЖДОЙ СТРОКИ ДЛЯ КАЖДОГО ЗАЯВЛЕНИЯ	Определяет, должна ли процедура триггера запускаться один раз для каждой строки, затронутой триггером событие или только один раз для каждого оператора SQL. Если ни один из них не указан, по умолчанию используется FOR EACH STATEMENT.
expression	Логическое выражение, определяющее, будет ли функция триггера на самом деле быть казненным.
trigger-step	Действие для триггера, это оператор sql.

Существует два расширения SQLite для триггеров « OLD » и « NEW ‘. OLD и NEW не чувствительны к регистру.

Ключевые слова OLD и NEW в теле триггера позволяют получить доступ к столбцам в строках, на которые действует триггер.
В триггере INSERT можно использовать только NEW.col_name.
В триггере UPDATE вы можете использовать OLD.col_name для ссылки на столбцы строки перед ее обновлением и NEW .col_name для ссылки на столбцы строки после ее обновления.
В триггере DELETE можно использовать только OLD.col_name; нет новой строки.

Пример базы данных, таблица, структура таблицы, записи таблицы для различных примеров

Триггер SQLite: пример ПОСЛЕ ВСТАВКИ

В следующем примере у нас есть две таблицы: emp_details и emp_log. Чтобы вставить некоторую информацию в таблицу emp_logs (которая имеет три поля emp_id и salary и edttime) каждый раз, когда INSERT происходит в таблице emp_details, мы использовали следующий триггер:

Вот триггер ins_same_rec:

  СОЗДАТЬ ТРИГГЕР aft_insert ПОСЛЕ ВСТАВКИ НА emp_detailsBEGININSERT INTO emp_log (emp_id, salary, edittime) ЗНАЧЕНИЯ (NEW.employee_id, NEW. salary, current_date); END;

Записи таблицы (в некоторых столбцах):

sqlite > ВЫБЕРИТЕ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; employee_id first_name last_name job_id salary Commission_pct ----------- ---------- ------- --- ---------- ---------- -------------- 100 Стивен Кинг AD_PRES 24000101 Нина Кочхар AD_VP 17000102 Лекс Де Хаан AD_VP 17000103 Александр Хунольд IT_PROG 9000104 Брюс Эрнст IT_PROG 6000105 Дэвид Остин IT_PROG 4800106 Валли Патабалла IT_PROG 4800107 Диана Лоренц IT_PROG 4200108 Нэнси Гринберг FI_MGR 12000109 Дэниел Фэвиет FI_ACCOUNT MancoUNT 9000100 FIACCOUNT 7800110 ISACCOUNT 7771107 > Записи таблицы (все столбцы):

 sqlite> SELECT * FROM emp_log; emp_id salary edittime ---------- -  --------- ---------- 100 24000 2011-01-15101 17000 2010-01-12102 17000 2010-09-22103 9000 2011-06-  21104 6000 2012-07-05105 4800 2011-06-02

Теперь вставьте одну запись в таблицу emp_details, чтобы увидеть записи в таблицах emp_details и emp_log:

  INSERT INTO emp_details VALUES (236, 'RABI', 'CHANDRA', 'RABI', '590.423.45700', '2013-01-12', 'AD_VP', 15000, .5, NULL, NULL);

 sqlite> ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; employee_id first_name last_name job_id salary Commission_pct ----------- ---  ------- ---------- ---------- ---------- -------------  -100 Стивен Кинг AD_PRES 24000101 Neena Kochhar AD_VP 17000102 Lex De Haan AD_VP 17000103 Александр Hunold IT_PROG 9000104 Брюс Эрнст IT_PROG 6000105 Дэвид Остин IT_PROG 4800106 Valli Pataballa IT_PROG 4800107 Диана лоренц IT_PROG 4200108 Нэнси Гринберг FI_MGR 12000109 Daniel Faviet FI_ACCOUNT 9000110 John Chen FI_ACCOUNT 8200111 Исмаэль Sciarra FI_ACCOUNT  7700112 Jose Manue Urman FI_ACCOUNT 7800236 RABI CHANDRA AD_VP 15000 0,5

 sqlite> SEL  ECT * FROM emp_log; emp_id salary edittime ---------- ---------- ---------- 100 24000 2011-01-15101 17000 2010-01-  12102 17000 2010-09-22103 9000 2011-06-21104 6000 2012-07-05105 4800 2011-06-02236 15000 2014-10-13

itemscope itemtype = "http ://schema.org/WebPageElement/Heading "> Триггер SQLite: пример перед вставкой

В следующем примере перед вставкой новой записи в таблицу emp_details триггер проверяет столбец значение FIRST_NAME, LAST_NAME, JOB_ID и
- если есть какие-либо пробелы до или после FIRST_NAME, LAST_NAME, функция LTRIM () удалит их.
- Значение JOB_ID будет быть преобразованы в верхний регистр функцией UPPER ().

Вот триггер befo_insert:

  CREATE TRIGGER befo_insert BEFORE INSERT ON emp_detailsBEGINSELECT CASE WHEN ((SELECT emp_details.  employee_id ОТ emp_details ГДЕ emp_details.employee_id = NEW. employee_id) ISNULL) THEN RAISE (ABORT, 'Это сообщение об ошибке, определяемое пользователем - этот идентификатор employee_id не существует.') END;  END;

Теперь вставьте строку в таблицу emp_details (проверьте столбец employee_id, существует он или нет.):

  INSERT INTO emp_details (employee_id, first_name, last_name) values (250, 'Jeson', 'Flap');

Теперь вот результат.

 sqlite> INSERT INTO emp_details (employee_id, first_name, last_name) values (250, 'Jeson', 'Flap'); Ошибка: это сообщение об ошибке, определяемое пользователем - этот идентификатор сотрудника не существует.

Триггер SQLite: пример ПОСЛЕ ОБНОВЛЕНИЯ

У нас есть две таблицы student_mast и stu_log. student_mast имеет три столбца STUDENT_ID, NAME, ST_CLASS. Таблица stu_log имеет два столбца user_id и description.

 sqlite> SELECT * FROM student_mast; имя студента st_class ---------- ---------------------  --------- ---------- 1 Стивен Кинг 72 Нина Кочхар 83 Лекс Де Хаан 94 Александр Хунольд 10

Давайте продвинем всех учеников в следующем class, то есть 7 будет 8, 8 будет 9 и так далее. После обновления одной строки в таблице student_mast новая строка будет вставлена в таблицу stu_log, где мы будем хранить текущий идентификатор пользователя и небольшое описание текущего обновления. Вот код триггера:

Вот триггер для этого события -

  CREATE TRIGGER aft_update AFTER UPDATE ON student_mastBEGININSERT в значения stu_log (description)  ('Обновить запись ученика' || OLD.NAME || 'Предыдущий класс:' || OLD.ST_CLASS || 'Настоящий класс' || NEW.st_class); END;

Теперь обновите таблицу student_mast:

  UPDATE student_mast SET st_class = st_class + 1;

Триггер показывает вам обновленные записи в "stu_log". Вот последнее положение таблиц student_mast и stu_log:

 sqlite> SELECT * FROM student_mast; student_id name st_class ---------- ----------  ---------- ---------- 1 Стивен Кинг 82 Нина Кочхар 93 Лекс Де Хаан 104 Александр Хунольд 11

 sqlite> ВЫБРАТЬ описание ИЗ stu_log;  описание-------------------------------------------------  ------------------------------------- Обновить запись ученика Стивен Кинг Предыдущий класс: 7 Настоящий класс 8Обновить ученика  Запись Нина Кочхар Предыдущий класс: 8 Текущий класс 9 Обновить запись ученика Лекс Де Хаан Предыдущий класс: 9 Текущий класс 10 Обновить запись ученика Александр Хунольд Предыдущий класс: 10 Настоящий класс 11sqlite> SELECT * FROM student_mast;

SQLite Trigger: пример ДО ОБНОВЛЕНИЯ

У нас есть две таблицы student_mast и student_marks. Вот примеры таблиц ниже. Столбец student_id таблицы student_mast является первичным ключом, а в таблице student_marks это внешний ключ, ссылка на столбец student_id таблицы student_mast.

 Таблица - student_mast; student_id name st_class ---------- ------------------------  ------ ---------- 1 Стивен Кинг 72 Нина Кочхар 83 Лекс Де Хаан 94 Александр Хунольд 10 Таблица - student_marksstudent_id name sub1 sub2 ---------- -----  --------------- ---------- --------- 1 Стивен Кинг2 Нина Кочхар3 Лекс Де Хаан4 Александр Хунольд

Вот триггер

  CREATE TRIGGER befo_update BEFORE UPDATE ON student_mastBEGINSELECT CASE WHEN ((SELECT student_id FROM student_marks WHERE student_id = NEW.student_id) ISNULL) THEN RAISE (ABORT,  «Это сообщение об ошибке, определяемое пользователем - этот идентификатор не может быть обновлен.») END;  END;

Теперь мы собираемся обновить столбец первичного ключа таблицы student_mast и посмотреть результат ниже.

 sqlite> UPDATE student_mast SET student_id =  10 WHERE st_class = 9; Ошибка: это сообщение об ошибке, определяемое пользователем - этот идентификатор не может быть обновлен.

Триггер SQLite: пример ПОСЛЕ УДАЛЕНИЯ

В нашем примере «ПОСЛЕ ОБНОВЛЕНИЯ» у нас было две таблицы student_mast и stu_log. student_mast имеет три столбца STUDENT_ID, NAME, ST_CLASS, а таблица stu_log имеет два столбца user_id и description. Мы хотим сохранить некоторую информацию в таблице stu_log после того, как в таблице student_mast произошла операция удаления. Вот триггер:

Вот триггер

  СОЗДАТЬ ТРИГГЕР aft_delete ПОСЛЕ УДАЛЕНИЯ НА student_mast НАЧАТЬ ВСТАВИТЬ в stu_log (описание) ЗНАЧЕНИЯ ('Обновить  Студенческая запись '|| OLD.NAME ||' Класс: '|| OLD.ST_CLASS ||' -> Удалено '|| date (' NOW '));  END;

Разрешить удаление студента из student_mast

  sqlite> УДАЛИТЬ ИЗ STUDENT_MAST WHERE STUDENT_ID = 1;

Вот последнее положение таблиц student_mast, stu_log:

 sqlite> SELECT * FROM STUDENT_MAST; имя студента st_class ---------- -  ------------------ --------- 2 Нина Кочхар 93 Лекс Де Хаан 104 Александр Хунольд 11sqlite> ВЫБРАТЬ описание ИЗ stu_log; описание -----  --------------------------------------------------  ----------------------------- Обновить запись ученика Стивен Кинг Предыдущий класс: 7 Настоящее время Класс 8 Обновить запись ученика Нина Кочхар Предыдущий класс: 8 Настоящее время  Класс 9 Обновить запись ученика Лекс Де Хаан Предыдущий класс: 9 Текущий класс 10 Обновить запись ученика Александр Хунольд Предыдущий класс: 10 Текущий класс 11 Обновить запись ученика Стивен Кинг Класс: 8 -> Удален 13.10.2014

Триггер SQLite: пример перед удалением

 Таблица - student_mast; student_id name st_class ---------- ------------------------  ------ ---------- 1 Стивен Кинг 72 Нина Кочхар 83 Лекс Де Хаан 94 Александр Хунольд 10 Таблица - student_marksstudent_id name sub1 sub2 ---------- -----  --------------- ---------- --------- 1 Стивен Кинг2 Нина Кочхар3 Лекс Де Хаан4 Александр Хунольд

Вот триггер

  СОЗДАТЬ ТРИГГЕР befo_delete ПРЕЖДЕ ЧЕМ УДАЛИТЬ НА student_marks НАЧАТЬ ВЫБРАТЬ СЛУЧАЙ КОГДА (ВЫБРАТЬ СЧЕТЧИК (идентификатор студента) FROM student_mast ГДЕ student_id = OLD.student_id)> 0 ЗАТЕМ ПОДНЯТЬ (ОТМЕНА  , 'Нарушение внешнего ключа: строка со ссылкой на строки student_masts должна быть удалена.') END;  END;

Давайте попробуем удалить студента из student_marks и посмотрим результат.

 sqlite> УДАЛИТЬ ИЗ student_marks WHERE name = 'Steven King'; Ошибка: Нарушение внешнего ключа: ссылка на строки student_masts должна быть удалена.

Триггер SQLite с использованием INSTEAD OF

Вот пример таблицы emp_details.

 employee_id first_name last_name email ----------- -  --------- ---------- ---------- 100 Стивен Кинг СКИНГ 101 Нина Кочхар НКОЧАР 102 Лекс Де Хаан ЛДЕХААН 103 Александр Хунольд АХУНОЛЬД 104 Брюс Эрнст БЕРНСТ  105 Дэвид Остин ДАУСТИН 106 Валли Патабалла VPATABAL 107 Диана Лоренц ДЛОРЕНЦ 108 Нэнси Гринберг НГРИНБЕ 109 Даниэль Фэвиет ДФАВЬЕТ 110 Джон Чен ЙЧЕН 111 Исмаэль Скиарра ИСКИАРРА 112 Хосе Мануэ Урман ДЖМУРМАН 236 РАБИ ЧАНДРА РАБИ

имя emp_details_view.

  СОЗДАТЬ ПРОСМОТР emp_details_viewASSELECT employee_id, first_name, last_name, emailFROM emp_detailsORDER BY first_name, last_name;

Теперь посмотрим на только что созданное представление.

 sqlite> ВЫБРАТЬ имя FROM sqlite_master WHERE type = 'view'; nameemp_details_view

Вот представление.

 employee_id first_name last_name email  ----------- ---------- ---------- ---------- 103 Александр Хунольд AHUNOLD104 Брюс Эрнст BERNST109 Даниэль Фавье  DFAVIET105 Дэвид Остин DAUSTIN107 Diana Lorentz DLORENTZ111 Ismael Sciarra ISCIARRA110 John Chen JCHEN112 Jose Manue Urman JMURMAN102 Lex De Haan LDEHAAN108 Nancy Greenberg NGREENBE101 Neena Kochhar Stevenll

ВПРЕЛЬСКИЙ ВСТАВИТЬ ТРИГГЕР, используя ВМЕСТО

Вот пример

  СОЗДАТЬ ТРИГГЕР view_ins_trig ВМЕСТО ВСТАВКИ НА emp_details_view НАЧАТЬ ВСТАВИТЬ В emp_details (employee_id  , first_name, last_name, email) ВЫБРАТЬ new.employee_id, new.first_name, new.last_name, new.email;  END;

Теперь вставьте строки в emp_details_view, и триггеры распространят эти изменения в базовую таблицу..

  INSERT INTO emp_details_view (employee_id, first_name, last_name, email) VALUES (250, 'Andrai', 'Marku', 'and_mar');

Теперь посмотрите представление и базовую таблицу

 базовую таблицу - emp_detailsemployee_id first_name last_name email --------------------  ---------- ---------- ---------- 100 Стивен Кинг SKING101 Neena Kochhar NKOCHHAR102 Lex De Haan LDEHAAN103 Alexander Hunold AHUNOLD104 Брюс Эрнст BERNST105 Дэвид Остин DAUSTIN106  Валли Патабалла VPATABAL107 Diana Lorentz DLORENTZ108 Nancy Greenberg NGREENBE109 Daniel Faviet DFAVIET110 John Chen JCHEN111 Ismael Sciarra ISCIARRA112 Jose Manue Urman JMURMAN236 RABI CHANDRAee_view - имя_последнего сотрудника RABI250 - имя_последнего сотрудника_представитель_этапа RABI250 Andrai Mark  --- ---------- ---------- ---------- 103 Александр Хунольд AHUNOLD250 Андрай Марку and_mar104 Брюс Эрнст BERNST109 Дэниел Фэвиет DFAVIET105 Дэвид Остин DAUSTIN107 Диана  Лоренц DLORENTZ111 Ismael Sciarra ISCIARRA110 John Chen JCHEN112 Jose Ma  nue Urman JMURMAN102 Lex De Haan LDEHAAN108 Nancy Greenberg NGREENBE101 Neena Kochhar NKOCHHAR236 RABI CHANDRA RABI100 Стивен Кинг SKING106 Valli Pataballa VPATABAL

ОБНОВЛЕНИЕ с использованием TRIGGER. # p> Вот пример

  СОЗДАТЬ ТРИГГЕР view_update_trig ВМЕСТО ОБНОВЛЕНИЯ НА emp_details_view НАЧАТЬ ОБНОВЛЕНИЕ emp_details SET employee_id = new.employee_id, first_name = new.first_name, last_name = new.  last_name WHERE employee_id = old.employee_id;  END;

Теперь обновите строки в emp_details_view.

  ОБНОВЛЕНИЕ emp_details_view SET first_name = 'Andrai' WHERE first_name = 'RABI' AND last_name = 'CHANDRA';

Теперь посмотрим представление и базовая таблица

 базовая таблица - emp_detailsemployee_id first_name last_name email -------------------- --------  - ---------- ---------- 100 Стивен Кинг SKING101 Neena Kochhar NKOCHHAR102 Lex De Haan LDEHAAN103 Alexander Hunold AHUNOLD104 Bruce Ernst BERNST105 David Austin DAUSTIN106 Valli Pataballa VPATABAL107 Diana Lorentz DLORENTZ108 Nancy Greenberg  NGREENBE109 Daniel Faviet DFAVIET110 John Chen JCHEN111 Ismael Sciarra ISCIARRA112 Jose Manue Urman JMURMAN236 Andrai CHANDRA RABIview - emp_details_viewemployee_id first_name last_name email -------------------- --------  - ---------- ---------- 103 Александр Хунольд AHUNOLD236 Andrai CHANDRA RABI104 Брюс Эрнст BERNST109 Daniel Faviet DFAVIET105 Дэвид Остин DAUSTIN107 Diana Lorentz DLORENTZ111 Ismael Sciarra ISCIARRA110 John Chen JCHEN112 Jose Manue Urman  JMURMAN102 Lex De Haan  LDEHAAN108 Nancy Greenberg NGREENBE101 Neena Kochhar NKOCHHAR100 Стивен Кинг SKING106 Valli Pataballa VPATABAL

УДАЛИТЬ ТРИГГЕР с помощью INSTEAD OF

Вот пример

  СОЗДАТЬ ТРИГГЕР view_delete_trig ВМЕСТО удаления ON emp_details_view НАЧАТЬ УДАЛИТЬ ИЗ emp_details ГДЕ employee_id = old. employee_id;  END;

Теперь удалите строку из emp_details_view, у которой идентификатор employee_id равен 106, и посмотрите результат.

  УДАЛИТЬ ИЗ emp_details_view ГДЕ employee_id = 106;

Теперь посмотрите представление и базовую таблицу

 базовая таблица - emp_detailsemployee_id first_name last_name email -------------  ------- ---------- ---------- ---------- 100 Стивен Кинг SKING101 Neena Kochhar NKOCHHAR102 Lex De Haan LDEHAAN103 Alexander Hunold  AHUNOLD104 Брюс Эрнст BERNST105 Дэвид Остин DAUSTIN107 Diana Lorentz DLORENTZ108 Nancy Greenberg NGREENBE109 Daniel Faviet DFAVIET110 John Chen JCHEN111 Ismael Sciarra ISCIARRA112 Jose_ Manue Urman REMPID_имя_последнее имя_эмпа JeeURMAN236 Andrai  ---- ---------- ---------- ---------- 103 Александр Хунольд AHUNOLD236 Andrai CHANDRA RABI104 Брюс Эрнст BERNST109 Daniel Faviet DFAVIET105 Дэвид Остин DAUSTIN107  Diana Lorentz DLORENTZ111 Ismael Sciarra ISCIARRA110 John Chen JCHEN112 Jose Manue Urman JMURMAN102 Lex De Haan LDEHAAN108 Nancy Greenberg NGREENBE101 Neena Kochhar NKOCHHAR100 Steven King SKING

удален 106.

DROP триггера SQLite

Чтобы удалить или уничтожить триггер, используйте оператор DROP TRIGGER. Для выполнения этой команды текущий пользователь должен быть владельцем таблицы, для которой определен триггер.

Синтаксис:

 DROP TRIGGER trigger_name

Пример:

Если вы удалите или отбросите только что созданный триггер delete_stu, можно использовать следующую инструкцию:

  DROP TRIGGER delete_stu на student_mast;

Триггер delete_stu будет удален.