Триггеры 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 создает новый триггер в SQLite. Инструкция CREATE TRIGGER используется для добавления триггеров в схему базы данных. Триггеры - это операции с базой данных, которые автоматически выполняются, когда происходит указанное событие базы данных.

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

Синтаксис:

 СОЗДАТЬ [ТЕМП | TEMPORARY] TRIGGER имя-триггер
[ДО | ПОСЛЕ] событие базы данных ВКЛ [имя-базы-данных.] Имя-таблицы
спусковой механизм действия
Триггер-действие далее определяется как: 
[ДЛЯ КАЖДОГО РЯДА | ДЛЯ КАЖДОГО ЗАЯВЛЕНИЯ] [КОГДА выражение]
НАЧАТЬ
инициировать шаг; [пошаговый шаг;] *
КОНЕЦ

Параметры:

Существует два расширения SQLite для триггеров « СТАРЫЙ » и « НОВЫЙ ». СТАРЫЙ и НОВЫЙ не чувствительны к регистру.

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

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

emp_details

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

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

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

CREATE TRIGGER aft_insert AFTER INSERT ON emp_details
BEGIN
INSERT INTO emp_log(emp_id,salary,edittime)
         VALUES(NEW.employee_id,NEW.salary,current_date);
END;

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

 sqlite> ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
ИД сотрудника имя_ фамилии Фамилия задания № зарплаты комиссионное вознаграждение
----------- ---------- ---------- ---------- --------- - --------------
100 Стивен Кинг AD_PRES 24000
101 Neena Kochhar AD_VP 17000
102 Lex De Haan AD_VP 17000
103 Александр Хунольд IT_PROG 9000
104 Брюс Эрнст IT_PROG 6000
105 Дэвид Остин IT_PROG 4800
106 Valli Pataballa IT_PROG 4800
107 Диана Лоренц IT_PROG 4200
108 Нэнси Гринберг FI_MGR 12000
109 Даниэль Фавиет FI_ACCOUNT 9000
110 Джон Чен FI_ACCOUNT 8200
111 Исмаэль Скарра FI_ACCOUNT 7700
112 Хосе Мануэ Урман FI_ACCOUNT 7800

Записи таблицы (все столбцы): emp_log

 sqlite> SELECT * FROM emp_log;
время редактирования зарплаты emp_id
---------- ---------- ----------
100 24000 2011-01-15
101 17000 2010-01-12
102 17000 2010-09-22
103 9000 2011-06-21
104 6000 2012-07-05
105 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;
ИД сотрудника имя_ фамилии Фамилия задания № зарплаты комиссионное вознаграждение
----------- ---------- ---------- ---------- --------- - --------------
100 Стивен Кинг AD_PRES 24000
101 Neena Kochhar AD_VP 17000
102 Lex De Haan AD_VP 17000
103 Александр Хунольд IT_PROG 9000
104 Брюс Эрнст IT_PROG 6000
105 Дэвид Остин IT_PROG 4800
106 Valli Pataballa IT_PROG 4800
107 Диана Лоренц IT_PROG 4200
108 Нэнси Гринберг FI_MGR 12000
109 Даниэль Фавиет FI_ACCOUNT 9000
110 Джон Чен FI_ACCOUNT 8200
111 Исмаэль Скарра FI_ACCOUNT 7700
112 Хосе Мануэ Урман FI_ACCOUNT 7800
236 RABI CHANDRA AD_VP 15000 0,5

 sqlite> SELECT * FROM emp_log;
время редактирования зарплаты emp_id
---------- ---------- ----------
100 24000 2011-01-15
101 17000 2010-01-12
102 17000 2010-09-22
103 9000 2011-06-21
104 6000 2012-07-05
105 4800 2011-06-02
236 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 ().

CREATE TRIGGER befo_insert BEFORE INSERT ON emp_details
BEGIN
SELECT CASE 
WHEN ((SELECT emp_details . employee_id FROM emp_details WHERE emp_details.employee_id = NEW.employee_id ) ISNULL) 
THEN RAISE(ABORT, 'This is an User Define Error Message - This employee_id does not exist.') 
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) значения (250, 'Jeson', 'Flap');
Ошибка: это сообщение об ошибке, определяемое пользователем. Этот employee_id не существует.

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

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

 sqlite> SELECT * FROM student_mast;
имя студента
---------- ------------------------------ ----------
1 Стивен Кинг 7
2 Нина Коххар 8
3 Лекс Де Хаан 9
4 Александр Хунольд 10

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

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

CREATE TRIGGER aft_update AFTER UPDATE ON student_mast
BEGIN
INSERT into stu_log (description) values('Update Student Record '||
         OLD.NAME || '    Previous Class : '||OLD.ST_CLASS ||'    Present 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;
имя студента
---------- -------------------- ----------
1 Стивен Кинг 8
2 Неена Коххар 9
3 Лекс Де Хаан 10
4 Александр Хунольд 11

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

Триггер SQLite: пример перед обновлением

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

 Таблица - student_mast;
имя студента
---------- ------------------------------ ----------
1 Стивен Кинг 7
2 Нина Коххар 8
3 Лекс Де Хаан 9
4 Александр Хунольд 10
Таблица - Student_marks
имя студента sub1 sub1 sub2
---------- -------------------- ---------- ---------
1 Стивен Кинг
2 Нина Кочхар
3 Лекс Де Хаан
4 Александр Хунольд

Вот триггер

CREATE TRIGGER befo_update BEFORE UPDATE ON student_mast
BEGIN
SELECT CASE 
WHEN ((SELECT student_id FROM student_marks WHERE student_id = NEW.student_id ) ISNULL) 
THEN RAISE(ABORT, 'This is a User Define Error Message - This ID can not be updated.') 
END; 
END;

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

 sqlite> ОБНОВЛЕНИЕ student_mast SET student_id = 10 ГДЕ st_class = 9;
Ошибка: это сообщение об ошибке определения пользователя - этот идентификатор не может быть обновлен.

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

В нашем примере «AFTER UPDATE» у нас было две таблицы student_mast и stu_log. student_mast состоит из трех столбцов STUDENT_ID, NAME, ST_CLASS и таблица stu_log имеет два столбца user_id и description. Мы хотим сохранить некоторую информацию в таблице stu_log после того, как в таблице student_mast произошла операция удаления. Вот триггер:

Вот триггер

CREATE TRIGGER aft_delete AFTER DELETE ON student_mast 
BEGIN 
INSERT into stu_log (description) VALUES ('Update Student Record '||
         OLD.NAME||' Class : '||OLD.ST_CLASS||' -> Deleted on  '||
         date('NOW')); 
END;

Позвольте удалить студента из student_mast

sqlite> DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1;

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

 sqlite> SELECT * FROM STUDENT_MAST;
имя студента
---------- -------------------- ---------
2 Неена Коххар 9
3 Лекс Де Хаан 10
4 Александр Гунольд 11
sqlite> ВЫБРАТЬ описание ОТ stu_log;
описание
-------------------------------------------------- ----------------------------------
Обновление студенческих записей Стивена Кинга Предыдущий класс: 7 Настоящий класс 8
Обновить студенческий билет Neena Kochhar Предыдущий класс: 8 Текущий класс 9
Обновить студенческие записи Lex De Haan Предыдущий класс: 9 Настоящий класс 10
Обновить студенческие записи Александра Хунольда Предыдущий класс: 10 Настоящий класс 11
Обновление студенческого рекорда Стивена Кинга Класс: 8 -> Удалено 2014-10-13

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

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

 Таблица - student_mast;
имя студента
---------- ------------------------------ ----------
1 Стивен Кинг 7
2 Нина Коххар 8
3 Лекс Де Хаан 9
4 Александр Хунольд 10
Таблица - Student_marks
имя студента sub1 sub1 sub2
---------- -------------------- ---------- ---------
1 Стивен Кинг
2 Нина Кочхар
3 Лекс Де Хаан
4 Александр Хунольд

Вот триггер

CREATE TRIGGER befo_delete BEFORE DELETE ON student_marks
BEGIN
SELECT CASE 
WHEN (SELECT COUNT(student_id) FROM student_mast WHERE student_id=OLD.student_id) > 0
THEN RAISE(ABORT,
'Foreign Key Violation: student_masts rows reference row to be deleted.')
END; 
END;

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

 sqlite> DELETE FROM student_marks WHERE name = 'Стивен Кинг';
Ошибка: Нарушение внешнего ключа: строка_стадии ссылается на строку, подлежащую удалению.

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

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

 сотрудник_ид имя_фамилия фамилия электронная почта      
----------- ---------- ---------- ---------- 
100 Стивен Кинг СКИНГ      
101 Нина Кочхар НКОЧХАР   
102 Лекс Де Хаан LDEHAAN    
103 Александр Хунольд AHUNOLD    
104 Брюс Эрнст БЕРНСТ     
105 Дэвид Остин ДАУСТИН    
106 Valli Pataballa VPATABAL   
107 Диана Лоренц ДЛОРЕНЦ   
108 Нэнси Гринберг НГРИНБЕ   
109 Даниэль Фавиет ДФАВЬЕТ    
110 Джон Чен JCHEN      
111 Исмаэль Скиарра ИСКЬАРРА   
112 Хосе Мануэ Урман JMURMAN    
236 РАБИ ЧАНДРА РАБИ       

Теперь создайте имя представления emp_details_view.

CREATE VIEW emp_details_view
AS
SELECT employee_id,first_name,last_name,email
FROM emp_details
ORDER BY first_name,last_name;

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

 sqlite> ВЫБЕРИТЕ имя ОТ sqlite_master WHERE type = 'view';
название
emp_details_view

Вот мнение.

 сотрудник_ид имя_фамилия фамилия электронная почта
----------- ---------- ---------- ----------
103 Александр Хунольд AHUNOLD
104 Брюс Эрнст БЕРНСТ
109 Даниэль Фавиет ДФАВЬЕТ
105 Дэвид Остин ДАУСТИН
107 Диана Лоренц ДЛОРЕНЦ
111 Исмаэль Скиарра ИСКЬАРРА
110 Джон Чен JCHEN
112 Хосе Мануэ Урман JMURMAN
102 Лекс Де Хаан LDEHAAN
108 Нэнси Гринберг НГРИНБЕ
101 Нина Кочхар НКОЧХАР
236 РАБИ ЧАНДРА РАБИ
100 Стивен Кинг СКИНГ
106 Valli Pataballa VPATABAL

ВСТАВИТЬ ТРИГГЕР, используя INSTEAD OF

Вот пример

CREATE TRIGGER view_ins_trig 
INSTEAD OF INSERT 
ON emp_details_view 
BEGIN
        INSERT INTO emp_details(employee_id,first_name,last_name,email) 
        SELECT 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_details
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
100 Стивен Кинг СКИНГ
101 Нина Кочхар НКОЧХАР
102 Лекс Де Хаан LDEHAAN
103 Александр Хунольд AHUNOLD
104 Брюс Эрнст БЕРНСТ
105 Дэвид Остин ДАУСТИН
106 Valli Pataballa VPATABAL
107 Диана Лоренц ДЛОРЕНЦ
108 Нэнси Гринберг НГРИНБЕ
109 Даниэль Фавиет ДФАВЬЕТ
110 Джон Чен JCHEN
111 Исмаэль Скиарра ИСКЬАРРА
112 Хосе Мануэ Урман JMURMAN
236 РАБИ ЧАНДРА РАБИ
250 Andrai Marku and_mar
view - emp_details_view
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
103 Александр Хунольд AHUNOLD
250 Andrai Marku and_mar
104 Брюс Эрнст БЕРНСТ
109 Даниэль Фавиет ДФАВЬЕТ
105 Дэвид Остин ДАУСТИН
107 Диана Лоренц ДЛОРЕНЦ
111 Исмаэль Скиарра ИСКЬАРРА
110 Джон Чен JCHEN
112 Хосе Мануэ Урман JMURMAN
102 Лекс Де Хаан LDEHAAN
108 Нэнси Гринберг НГРИНБЕ
101 Нина Кочхар НКОЧХАР
236 РАБИ ЧАНДРА РАБИ
100 Стивен Кинг СКИНГ
106 Valli Pataballa VPATABAL

ОБНОВИТЬ ТРИГГЕР, используя INSTEAD OF

Вот пример

CREATE TRIGGER view_update_trig 
INSTEAD OF UPDATE 
ON emp_details_view 
BEGIN 
        UPDATE 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.

UPDATE emp_details_view SET first_name ='Andrai' 
WHERE first_name ='RABI' AND last_name='CHANDRA';
 

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

 базовая таблица - emp_details
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
100 Стивен Кинг СКИНГ
101 Нина Кочхар НКОЧХАР
102 Лекс Де Хаан LDEHAAN
103 Александр Хунольд AHUNOLD
104 Брюс Эрнст БЕРНСТ
105 Дэвид Остин ДАУСТИН
106 Valli Pataballa VPATABAL
107 Диана Лоренц ДЛОРЕНЦ
108 Нэнси Гринберг НГРИНБЕ
109 Даниэль Фавиет ДФАВЬЕТ
110 Джон Чен JCHEN
111 Исмаэль Скиарра ИСКЬАРРА
112 Хосе Мануэ Урман JMURMAN
236 Андрей ЧАНДРА РАБИ
view - emp_details_view
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
103 Александр Хунольд AHUNOLD
236 Андрей ЧАНДРА РАБИ
104 Брюс Эрнст БЕРНСТ
109 Даниэль Фавиет ДФАВЬЕТ
105 Дэвид Остин ДАУСТИН
107 Диана Лоренц ДЛОРЕНЦ
111 Исмаэль Скиарра ИСКЬАРРА
110 Джон Чен JCHEN
112 Хосе Мануэ Урман JMURMAN
102 Лекс Де Хаан LDEHAAN
108 Нэнси Гринберг НГРИНБЕ
101 Нина Кочхар НКОЧХАР
100 Стивен Кинг СКИНГ
106 Valli Pataballa VPATABAL

УДАЛИТЬ ТРИГГЕРА, используя INSTEAD OF

Вот пример

CREATE TRIGGER view_delete_trig 
INSTEAD OF delete 
ON emp_details_view
BEGIN 
        DELETE FROM emp_details 
		WHERE employee_id = old.employee_id; 
END;

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

DELETE FROM emp_details_view 
WHERE employee_id = 106; 

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

 базовая таблица - emp_details
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
100 Стивен Кинг СКИНГ
101 Нина Кочхар НКОЧХАР
102 Лекс Де Хаан LDEHAAN
103 Александр Хунольд AHUNOLD
104 Брюс Эрнст БЕРНСТ
105 Дэвид Остин ДАУСТИН
107 Диана Лоренц ДЛОРЕНЦ
108 Нэнси Гринберг НГРИНБЕ
109 Даниэль Фавиет ДФАВЬЕТ
110 Джон Чен JCHEN
111 Исмаэль Скиарра ИСКЬАРРА
112 Хосе Мануэ Урман JMURMAN
236 Андрей ЧАНДРА РАБИ
view - emp_details_view
сотрудник_ид имя_фамилия фамилия электронная почта
-------------------- ---------- ---------- ----------
103 Александр Хунольд AHUNOLD
236 Андрей ЧАНДРА РАБИ
104 Брюс Эрнст БЕРНСТ
109 Даниэль Фавиет ДФАВЬЕТ
105 Дэвид Остин ДАУСТИН
107 Диана Лоренц ДЛОРЕНЦ
111 Исмаэль Скиарра ИСКЬАРРА
110 Джон Чен JCHEN
112 Хосе Мануэ Урман JMURMAN
102 Лекс Де Хаан LDEHAAN
108 Нэнси Гринберг НГРИНБЕ
101 Нина Кочхар НКОЧХАР
100 Стивен Кинг СКИНГ

Была удалена строка, содержащая идентификатор employee_id 106.

УДАЛИТЬ триггер SQLite

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

Синтаксис:

 DROP TRIGGER имя-триггера

Пример:

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

DROP TRIGGER delete_stu on student_mast;

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

Предыдущая: Подзапросы
Далее: Упражнения SQLite Введение