Триггеры PostgreSQL

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

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

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

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

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

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

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

PostgreSQL: создать триггер

Триггер - это именованный объект базы данных, который связан с таблицей, и он активируется, когда происходит конкретное событие (например, вставка, обновление или удаление) для таблицы / представлений. Оператор CREATE TRIGGER создает новый триггер в PostgreSQL. Вот синтаксис:

Синтаксис:

 СОЗДАТЬ [ОГРАНИЧЕНИЕ] ТРИГГЕР имя {ДО | ПОСЛЕ | INSTEAD OF} {event [OR ...]}
    ON table_name
    [FROM referenced_table_name]
    [НЕ ЗАДЕРЖАЕТ | [НЕЗАВИСИМЫЙ] {ПЕРВОНАЧАЛЬНО НЕМЕДЛЕННЫЙ | ПЕРВОНАЧАЛЬНО ОТЛОЖЕН}
    [ДЛЯ [КАЖДОГО] {ROW | ЗАЯВЛЕНИЕ } ]
    [КОГДА (условие)]
    ВЫПОЛНИТЬ ПРОЦЕДУРУ имя_функции (аргументы)

параметры

Триггеры, которые определены для запуска триггерного события INSTEAD OF, должны быть помечены FOR EACH ROW и могут быть определены только для представлений. Триггеры ДО и ПОСЛЕ на виде должны быть помечены как ДЛЯ КАЖДОГО ЗАЯВЛЕНИЯ. Кроме того, триггеры могут быть определены для срабатывания для TRUNCATE, но только для КАЖДОГО ЗАЯВЛЕНИЯ. В следующей таблице приведены типы триггеров, которые можно использовать в таблицах и представлениях:

Вот простой пример триггерной функции.

Код:

CREATE OR REPLACE FUNCTION test()
  RETURNS trigger AS
$$
BEGIN
         INSERT INTO test_table(col1,col2,col3)
         VALUES(NEW.col1,NEW.col2,current_date);
 
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Теперь мы можем создать триггер, который будет запускать во время выполнения события, как указано в триггере для связанных таблиц.

Код:

CREATE TRIGGER test_trigger
  AFTER INSERT
  ON test_table
  FOR EACH ROW
  EXECUTE PROCEDURE test();
  

В вышеупомянутой триггерной функции есть новое ключевое слово « NEW », которое является расширением PostgreSQL для триггеров. Существует два расширения PostgreSQL для запуска « OLD » и « NEW ». СТАРЫЙ и НОВЫЙ не чувствительны к регистру.

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

Столбец с именем OLD доступен только для чтения. Вы можете ссылаться на него (если у вас есть привилегия SELECT), но не можете изменять его. Вы можете ссылаться на столбец с именем NEW, если у вас есть привилегия SELECT для него. В триггере BEFORE вы также можете изменить его значение с помощью SET NEW.col_name = value, если у вас есть привилегия UPDATE для него. Это означает, что вы можете использовать триггер для изменения значений, которые будут вставлены в новую строку или использованы для обновления строки. (Такой оператор SET не действует в триггере AFTER, поскольку изменение строки уже произошло.)

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

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

 postgres = # ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | имя_файла | фамилия | job_id | зарплата | COMMISSION_PCT
------------- + ------------- + ----------- + ---------- - + ---------- + ----------------
         100 | Стивен | Король | AD_PRES | 24000,00 | 0,00
         101 | Нина | Коххар | AD_VP | 17000,00 | 0,00
         102 | Лекс | Де Хаан | AD_VP | 17000,00 | 0,00
         103 | Александр | Hunold | IT_PROG | 9000,00 | 0,00
         104 | Брюс | Эрнст | IT_PROG | 6000,00 | 0,00
         105 | Дэвид | Остин | IT_PROG | 4800,00 | 0,00
         106 | Валли | Патабала | IT_PROG | 4800,00 | 0,00
         107 | Диана | Лоренц | IT_PROG | 4200,00 | 0,00
         108 | Нэнси | Гринберг | FI_MGR | 12000,00 | 0,00
         109 | Даниэль | Фавиет | FI_ACCOUNT | 9000,00 | 0,00
         110 | Джон | Чен | FI_ACCOUNT | 8200,00 | 0,00
         111 | Исмаэль | Sciarra | FI_ACCOUNT | 7700,00 | 0,00
         112 | Жозе Мануэль | Урман | FI_ACCOUNT | 7800,00 | 0,00
(13 рядов)

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

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

Сначала нужно создать триггерную функцию. Вот триггерная функция rec_insert ()

Код:

CREATE OR REPLACE FUNCTION rec_insert()
  RETURNS trigger AS
$$
BEGIN
         INSERT INTO emp_log(emp_id,salary,edittime)
         VALUES(NEW.employee_id,NEW.salary,current_date);
 
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

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

Код:

CREATE TRIGGER ins_same_rec
  AFTER INSERT
  ON emp_details
  FOR EACH ROW
  EXECUTE PROCEDURE rec_insert();
  

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

 postgres = # ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
employee_id | имя_файла | фамилия | job_id | зарплата | COMMISSION_PCT
------------- + ------------- + ----------- + ---------- - + ---------- + ----------------
         100 | Стивен | Король | AD_PRES | 24000,00 | 0,00
         101 | Нина | Коххар | AD_VP | 17000,00 | 0,00
         102 | Лекс | Де Хаан | AD_VP | 17000,00 | 0,00
         103 | Александр | Hunold | IT_PROG | 9000,00 | 0,00
         104 | Брюс | Эрнст | IT_PROG | 6000,00 | 0,00
         105 | Дэвид | Остин | IT_PROG | 4800,00 | 0,00
         106 | Валли | Патабала | IT_PROG | 4800,00 | 0,00
         107 | Диана | Лоренц | IT_PROG | 4200,00 | 0,00
         108 | Нэнси | Гринберг | FI_MGR | 12000,00 | 0,00
         109 | Даниэль | Фавиет | FI_ACCOUNT | 9000,00 | 0,00
         110 | Джон | Чен | FI_ACCOUNT | 8200,00 | 0,00
         111 | Исмаэль | Sciarra | FI_ACCOUNT | 7700,00 | 0,00
         112 | Жозе Мануэль | Урман | FI_ACCOUNT | 7800,00 | 0,00
(13 рядов)

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

 postgres = # SELECT * FROM emp_log;
 emp_id | зарплата | EDITTIME
-------- + -------- + ------------
    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
(6 рядов)

Теперь вставьте одну запись в таблицу 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);

 postgres = # ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | имя_файла | фамилия | job_id | зарплата | COMMISSION_PCT
------------- + ------------- + ----------- + ---------- - + ---------- + ----------------
         100 | Стивен | Король | AD_PRES | 24000,00 | 0,00
         101 | Нина | Коххар | AD_VP | 17000,00 | 0,00
         102 | Лекс | Де Хаан | AD_VP | 17000,00 | 0,00
         103 | Александр | Hunold | IT_PROG | 9000,00 | 0,00
         104 | Брюс | Эрнст | IT_PROG | 6000,00 | 0,00
         105 | Дэвид | Остин | IT_PROG | 4800,00 | 0,00
         106 | Валли | Патабала | IT_PROG | 4800,00 | 0,00
         107 | Диана | Лоренц | IT_PROG | 4200,00 | 0,00
         108 | Нэнси | Гринберг | FI_MGR | 12000,00 | 0,00
         109 | Даниэль | Фавиет | FI_ACCOUNT | 9000,00 | 0,00
         110 | Джон | Чен | FI_ACCOUNT | 8200,00 | 0,00
         111 | Исмаэль | Sciarra | FI_ACCOUNT | 7700,00 | 0,00
         112 | Жозе Мануэль | Урман | FI_ACCOUNT | 7800,00 | 0,00
         236 | РАБИ | ЧАНДРА | AD_VP | 15000,00 | 0,50
(14 рядов)

 postgres = # SELECT * FROM emp_log;
 emp_id | зарплата | EDITTIME
-------- + -------- + ------------
    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-09-15
(7 рядов)

Триггер PostgreSQL: пример перед вставкой

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

Код:

CREATE OR REPLACE FUNCTION befo_insert()
  RETURNS trigger AS
$$
BEGIN
NEW.FIRST_NAME = LTRIM(NEW.FIRST_NAME);
NEW.LAST_NAME = LTRIM(NEW.LAST_NAME);
NEW.JOB_ID = UPPER(NEW.JOB_ID);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Код:

CREATE TRIGGER che_val_befo_ins
  BEFORE INSERT
  ON emp_details
  FOR EACH ROW
  EXECUTE PROCEDURE befo_insert();
  

Теперь вставьте строку в таблицу emp_details (проверьте столбцы FIRST_NAME, LAST_NAME, JOB_ID):

Код:

INSERT INTO emp_details VALUES (334, ' Ana ', ' King', 'ANA', 
'690.432.45701', '2013-02-05', 'it_prog', 17000, .50);

Теперь перечислите следующие поля emp_details:

 postgres = # ВЫБРАТЬ EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | имя_файла | фамилия | job_id | зарплата | COMMISSION_PCT
------------- + ------------- + ----------- + ---------- - + ---------- + ----------------
         100 | Стивен | Король | AD_PRES | 24000,00 | 0,00
         101 | Нина | Коххар | AD_VP | 17000,00 | 0,00
         102 | Лекс | Де Хаан | AD_VP | 17000,00 | 0,00
         103 | Александр | Hunold | IT_PROG | 9000,00 | 0,00
         104 | Брюс | Эрнст | IT_PROG | 6000,00 | 0,00
         105 | Дэвид | Остин | IT_PROG | 4800,00 | 0,00
         106 | Валли | Патабала | IT_PROG | 4800,00 | 0,00
         107 | Диана | Лоренц | IT_PROG | 4200,00 | 0,00
         108 | Нэнси | Гринберг | FI_MGR | 12000,00 | 0,00
         109 | Даниэль | Фавиет | FI_ACCOUNT | 9000,00 | 0,00
         110 | Джон | Чен | FI_ACCOUNT | 8200,00 | 0,00
         111 | Исмаэль | Sciarra | FI_ACCOUNT | 7700,00 | 0,00
         112 | Жозе Мануэль | Урман | FI_ACCOUNT | 7800,00 | 0,00
         236 | РАБИ | ЧАНДРА | AD_VP | 15000,00 | 0,50
         334 | Ана | Король | IT_PROG | 17000,00 | 0,50
(15 рядов)

Смотрите последний ряд:

 FIRST_NAME -> «Ана» заменена на «Ана» 
LAST_NAME -> «Король» изменен на «Король»
JOB_ID -> 'it_prog' изменено на 'IT_PROG' 

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

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

 postgres = # SELECT * FROM student_mast;
 student_id | имя | st_class
------------ + --------------------------- + --------- -
          1 | Стивен Кинг | 7
          2 | Нина Кочхар | 8
          3 | Лекс Де Хаан | 8
          4 | Александр Хунольд | 10
(4 ряда)

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

Код:

CREATE OR REPLACE FUNCTION aft_update()
  RETURNS trigger AS
$$
BEGIN
INSERT into stu_log VALUES (user, CONCAT('Update Student Record ',
         OLD.NAME,' Previous Class :',OLD.ST_CLASS,' Present Class ',
         NEW.st_class));
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

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

Код:

CREATE TRIGGER updt_log
  AFTER UPDATE
  ON student_mast
  FOR EACH ROW
  EXECUTE PROCEDURE aft_update();
  

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

Код:

UPDATE STUDENT_MAST SET ST_CLASS = ST_CLASS + 1;

Триггер показывает вам обновленные записи в «stu_log». Вот последняя позиция таблиц STUDENT_MAST и STU_LOG:

 postgres = # SELECT * FROM student_mast;
 student_id | имя | st_class
------------ + --------------------------- + --------- -
          1 | Стивен Кинг | 8
          2 | Нина Кочхар | 9
          3 | Лекс Де Хаан | 9
          4 | Александр Хунольд | 11
(4 ряда)

 postgres = # select * from stu_log;
          user_id | описание
--------------------------- + ---------------------- -------------------------------------------------- ------------------------------
 Postgres | Обновление студенческих записей Стивена Кинга Предыдущий класс: 7 Настоящий класс 8
 Postgres | Обновить студенческий билет Neena Kochhar Предыдущий класс: 8 Текущий класс 9
 Postgres | Обновить студенческие записи Lex De Haan Предыдущий класс: 8 Настоящий класс 9
 Postgres | Обновить студенческие записи Александра Хунольда Предыдущий класс: 10 Настоящий класс 11
(4 ряда)

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

У нас есть таблица student_marks с 10 столбцами и 4 строками. Данные есть только в столбцах STUDENT_ID и NAME.

 postgres = # SELECT * FROM STUDENT_MARKS;
 student_id | имя | sub1 | sub2 | sub3 | sub4 | sub5 | всего | per_marks | класс
------------ + --------------------------- + ------ + - ---- + ------ + ------ + ------ + ------- + ----------- + ---- ---
          1 | Стивен Кинг | | | | | | | |
          2 | Нина Кочхар | | | | | | | |
          3 | Лекс Де Хаан | | | | | | | |
          4 | Александр Хунольд | | | | | | | |
(4 ряда)

Теперь экзамен закончен, и мы получили все предметные оценки, теперь мы обновим таблицу, итоговые оценки по всем предметам, процент от общих оценок и оценки будет автоматически рассчитан. Для этого примера расчета принимаются следующие условия:

Общее количество баллов (будет храниться в столбце ВСЕГО): ВСЕГО = SUB1 + SUB2 + SUB3 + SUB4 + SUB5

Процент меток (будет храниться в столбце PER_MARKS): PER_MARKS = (TOTAL) / 5

Оценка (будет храниться столбец GRADE):

- Если PER_MARKS> = 90 -> «ОТЛИЧНО»

- Если PER_MARKS> = 75 И PER_MARKS <90 -> 'ОЧЕНЬ ХОРОШО'

- Если PER_MARKS> = 60 И PER_MARKS <75 -> «ХОРОШО»

- Если PER_MARKS> = 40 И PER_MARKS <60 -> «СРЕДНИЙ»

- Если PER_MARKS <40-> «НЕ РЕКЛАМНО»

Вот код:

Код:

UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87,
SUB5 = 59 WHERE STUDENT_ID = 1;

Позвольте обновить оценки ученика:

Вот триггерная функция befo_update:

Код:

CREATE OR REPLACE FUNCTION befo_update()
  RETURNS trigger AS
$$
BEGIN
NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; 
NEW.PER_MARKS = NEW.TOTAL/5;
IF NEW.PER_MARKS >=90 THEN
NEW.GRADE ='EXCELLENT';
ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
NEW.GRADE ='VERY GOOD';
ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
NEW.GRADE ='GOOD';
ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
NEW.GRADE ='AVERAGE';
ELSE
NEW.GRADE ='NOT PROMOTED';
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Вот триггер

Код:

CREATE TRIGGER updt_marks
  BEFORE UPDATE
  ON student_marks
  FOR EACH ROW
  EXECUTE PROCEDURE befo_update();
  

Теперь проверьте таблицу STUDENT_MARKS с обновленными данными. Триггер покажет вам обновленные записи в «stu_log».

 postgres = # SELECT * FROM STUDENT_MARKS;
 student_id | имя | sub1 | sub2 | sub3 | sub4 | sub5 | всего | per_marks | класс
------------ + --------------------------- + ------ + - ---- + ------ + ------ + ------ + ------- + ----------- + ---- ------------------
          2 | Нина Кочхар | | | | | | | |
          3 | Лекс Де Хаан | | | | | | | |
          4 | Александр Хунольд | | | | | | | |
          1 | Стивен Кинг | 54 | 69 | 89 | 87 | 59 | 358 | 71 | ХОРОШО
(4 ряда)

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

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

Код:

CREATE OR REPLACE FUNCTION aft_delete()
  RETURNS trigger AS
$$
BEGIN
INSERT into stu_log VALUES (user, CONCAT('Update Student Record ',
         OLD.NAME,' Class :',OLD.ST_CLASS,' -> Deleted on ',
         NOW()));
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Вот триггер

Код:

CREATE TRIGGER delete_stu
  AFTER DELETE
  ON student_mast
  FOR EACH ROW
  EXECUTE PROCEDURE aft_delete();
  

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

Код:

DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1;

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

 postgres = # SELECT * FROM STUDENT_MAST;
 student_id | имя | st_class
------------ + --------------------------- + --------- -
          2 | Нина Кочхар | 9
          3 | Лекс Де Хаан | 9
          4 | Александр Хунольд | 11
(3 ряда)
postgres = # select * from stu_log;
          user_id | описание
--------------------------- + ---------------------- -------------------------------------------------- ------------------------------
 Postgres | Обновление студенческих записей Стивена Кинга Предыдущий класс: 7 Настоящий класс 8
 Postgres | Обновить студенческий билет Neena Kochhar Предыдущий класс: 8 Текущий класс 9
 Postgres | Обновить студенческие записи Lex De Haan Предыдущий класс: 8 Настоящий класс 9
 Postgres | Обновить студенческие записи Александра Хунольда Предыдущий класс: 10 Настоящий класс 11
 Postgres | Обновление студенческого рекорда Стивена Кинга Класс: 7 -> Удалено 2014-09-16 16: 30: 35.093 + 05: 30
(5 рядов)

DROP триггер PostgreSQL

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

Синтаксис

 DROP TRIGGER [ЕСЛИ СУЩЕСТВУЕТ] name ON table_name [CASCADE | ОГРАНИЧЕНИЕ] 

параметры

Пример:

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

Код:

DROP TRIGGER delete_stu on student_mast;

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

Предыдущая: ВИДЫ
Далее: Введение в plpgSQL