# Занятие 17: PL/pgSQL / Триггеры

> Исходная страница в нативном Markdown: PL/pgSQL / Триггеры

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dev1_17_plpgsql_triggers.html](https://edu.postgrespro.ru/16/dev1-16/dev1_17_plpgsql_triggers.html)
> **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip)
> **Страниц PDF:** 32

## Page 1: PL/pgSQL / Триггеры {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Триггеры и триггерные функции
- В какой момент срабатывают триггеры
- Контекст выполнения триггерной функции
- Возвращаемое значение
- Для чего можно применять триггеры и для чего не нужно
- Событийные триггеры

## Page 3: Триггеры и функции {#source-page-003}

**Триггер**
  - объект базы данных — список обрабатываемых событий
  - при возникновении события вызывается триггерная функция
  - и ей передается контекст
- Триггерная функция
  - объект базы данных — код обработки события
  - выполняется в той же транзакции, что и основная операция
  - соглашение: функция не принимает параметры,
  - возвращает значение псевдотипа trigger (фактически record)
  - может использоваться в нескольких триггерах

### Комментарии преподавателя

Механизм триггеров позволяет выполнять определенные действия «в ответ» на определенные события. Триггер состоит из двух частей: собственно триггера (который определяет события) и триггерной функции (которая определяет действия). И триггер, и функция являются самостоятельными объектами БД.

Когда возникает событие, на которое «подписан» триггер, вызывается триггерная функция. Ей передается контекст вызова, чтобы можно было определить, какой именно триггер и в каких условиях вызвал функцию.

Триггерная функция — это обычная функция, которая написана с учетом некоторых соглашений:

● она пишется на любом языке, кроме чистого SQL;

● она не имеет параметров;

● она возвращает значение типа trigger (на самом деле это псевдотип, по факту возвращается запись, соответствующая строке таблицы).

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

<https://postgrespro.ru/docs/postgresql/16/trigger-definition>

## Page 4: События {#source-page-004}

INSERT, UPDATE, DELETE
  - таблицы
  - before/after statement
  - before/after row
  - представления
  - before/after statement
  - instead of
  - row
- TRUNCATE
  - таблицы
  - before/after statement
- Условие WHEN
  - устанавливает дополнительный фильтр

### Комментарии преподавателя

Триггеры могут срабатывать на вставку (INSERT), обновление (UPDATE) или удаление (DELETE) строк в таблице или представлении, а также на опустошение (TRUNCATE) таблиц.

Триггер может срабатывать до выполнения действия (BEFORE), после него (AFTER), или вместо него (INSTEAD OF).

Триггер может срабатывать один раз для всей операции (FOR EACH STATEMENT), или каждый раз для каждой затронутой строки (FOR EACH ROW).

Не для любой комбинации этих условий можно создать триггер. Например, instead-of-триггеры можно определить только для представлений на уровне строк, а truncate-триггер можно определить только для таблиц и только на уровне оператора. Допустимые варианты перечислены на слайде.

Кроме того, можно сузить область действия триггера, указав дополнительное условие WHEN: если условие не выполняется — триггер не срабатывает.

<https://postgrespro.ru/docs/postgresql/16/sql-createtrigger>

## Page 5: Before statement {#source-page-005}

**Срабатывает**
  - BEFORE STATEMENT
  - до операции
  - выполнение операции
- Возвращаемое значение
  - игнорируется
- Контекст
  - TG-переменные

### Комментарии преподавателя

Рассмотрим подробнее разные типы триггеров.

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

Возвращаемое значение триггерной функции игнорируется. Если в триггере возникает ошибка, операция отменяется.

Поскольку триггерная функция не имеет параметров, контекст вызова в PL/pgSQL передается ей с помощью предопределенных TG-переменных, таких, как:

● TG\_WHEN = «BEFORE»,

● TG\_LEVEL = «STATEMENT»,

● TG\_OP = «INSERT»/«UPDATE»/«DELETE»/«TRUNCATE»

и др. Триггерной функции можно также передать пользовательский контекст (аналог отсутствующих параметров) через переменную TG\_ARGV, хотя зачастую лучше вместо одной «обобщенной» функции создать несколько «частных».

<https://postgrespro.ru/docs/postgresql/16/plpgsql-trigger>

## Page 6: Before row {#source-page-006}

**Срабатывает**
  - BEFORE STATEMENT
  - перед действием со строкой
  - в процессе выполнения операции
  - выполнение операции
- Возвращаемое значение
  - BEFORE ROW
  - строка (возможно, измененная)
  - null отменяет действие
- Контекст
  - OLD
  - update, delete
  - NEW
  - insert, update
  - TG-переменные

### Комментарии преподавателя

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

В качестве контекста триггерная функция получает переменные:

● OLD — старая строка (не определено для операции вставки),

● NEW — измененная строка (не определено для удаления),

● TG\_WHEN = «BEFORE»,

● TG\_LEVEL = «ROW»,

● TG\_OP = «INSERT»/«UPDATE»/«DELETE»

и др.

Возврат неопределенного значения NULL воспринимается как отмена действия над данной строкой. Сама операция продолжит выполнение, но текущая строка не будет обработана и другие триггеры для этой строки не сработают.

Чтобы не вмешиваться в работу операции, триггер должен вернуть строку в том виде, в котором ее собирается изменить операция: NEW для вставки и обновления, любое значение (но не NULL) для удаления (обычно используют OLD).

Но триггерная функция может и изменить значение NEW, чтобы повлиять на результат операции — часто именно для этого такой триггер и создают.

## Page 7: Instead of row {#source-page-007}

**Срабатывает**
  - BEFORE STATEMENT
  - вместо действия со строкой
  - для представлений
  - выполнение операции
- Возвращаемое значение
  - BEFORE ROW
  - INSTEAD OF ROW
  - строка (возможно, измененная) —
  - будет видна в RETURNING
  - null отменяет действие
- Контекст
  - OLD
  - update, delete
  - NEW
  - insert, update
  - TG-переменные

### Комментарии преподавателя

Триггеры INSTEAD OF очень похожи на триггеры BEFORE, но определяются только для представлений и срабатывают не до, а вместо операции.

В задачу таких триггеров обычно входит выполнение необходимых операций над базовыми таблицами представления. Также триггер может вернуть измененное значение NEW — именно оно будет видно при выполнении операции с указанием фразы RETURNING.

## Page 8: After row {#source-page-008}

**Срабатывает**
  - BEFORE STATEMENT
  - после выполнения операции
  - очередь из прошедших условие WHEN
  - выполнение операции
- Возвращаемое значение
  - BEFORE ROW
  - INSTEAD OF ROW
  - игнорируется
- Контекст
  - OLD, OLD TABLE
  - update, delete
  - NEW, NEW TABLE
  - insert, update
  - TG-переменные
  - AFTER ROW

### Комментарии преподавателя

Триггеры AFTER ROW, как и BEFORE ROW, срабатывают для каждой затрагиваемой строки, но не сразу после действия над строкой, а после того, как выполнена вся операция - чтобы при обращении из этих триггеров к изменяющейся таблице результат не зависел от порядка обработки строк. Для этого события сначала помещаются в очередь и обрабатываются после окончания операции. Чем меньше событий попадет в очередь, тем меньше будет накладных расходов — поэтому именно в этом случае очень полезно использовать условие WHEN, чтобы отсечь заведомо ненужные строки.

Возвращаемое значение триггеров AFTER ROW игнорируется (поскольку операция уже выполнена).

Контекст триггерной функции составляют:

- OLD — старая строка (не определено для операции вставки),

- NEW — новое значение строки (не определено для удаления).

Кроме этих переменных, начиная с версии 10 триггерная функция может получить доступ к специальным переходным таблицам (transition tables). Таблица, указанная при создании триггера как OLD TABLE, содержит старые значения строк, обработанных триггером, а таблица NEW TABLE — новые значения тех же строк.

Доступны и обычные TG-переменные, включая:

● TG\_WHEN = «AFTER»,

● TG\_LEVEL = «ROW»,

● TG\_OP = «INSERT»/«UPDATE»/«DELETE».

## Page 9: After statement {#source-page-009}

**Срабатывает**
  - BEFORE STATEMENT
  - после операции
  - (даже если не затронута ни одна строка)
  - выполнение операции
- Возвращаемое значение
  - BEFORE ROW
  - INSTEAD OF ROW
  - игнорируется
- Контекст
  - OLD TABLE
  - update, delete
  - NEW TABLE
  - insert, update
  - TG-переменные
  - AFTER ROW
  - AFTER STATEMENT

### Комментарии преподавателя

Триггер AFTER STATEMENT срабатывает один раз после окончания операции и после всех триггеров AFTER ROW (независимо от того, сколько строк было затронуто).

Возвращаемое значение триггерной функции игнорируется.

Контекст вызова передается с помощью переходных таблиц. Обращаясь к ним, триггерная функция может проанализировать все затронутые строки. Обычно переходные таблицы используются именно с триггерами AFTER STATEMENT, а не AFTER ROW.

Также определены обычные TG-переменные:

● TG\_WHEN = «AFTER»,

● TG\_LEVEL = «STATEMENT»,

● TG\_OP = «INSERT»/«UPDATE»/«DELETE»/«TRUNCATE»

и др.

## Page 10: Порядок вызова триггеров {#source-page-010}

Создадим «универсальную» триггерную функцию, которая описывает контекст, в котором она вызвана. Контекст передается в различных TG-переменных.
Затем создадим триггеры на различные события, и будем смотреть, какие триггеры и в каком порядке вызываются при выполнении операций.
```sql
=> CREATE OR REPLACE FUNCTION describe() RETURNS trigger
AS $$
DECLARE
    rec record;
    str text := '';
BEGIN
    IF TG_LEVEL = 'ROW' THEN
        CASE TG_OP
            WHEN 'DELETE' THEN rec := OLD; str := OLD::text;
            WHEN 'UPDATE' THEN rec := NEW; str := OLD || ' -> ' || NEW;
            WHEN 'INSERT' THEN rec := NEW; str := NEW::text;
        END CASE;
    END IF;
    RAISE NOTICE '% % % %: %',
        TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, str;
    RETURN rec;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Таблица:
```sql
=> CREATE TABLE t(
    id integer PRIMARY KEY,
    s text
);
```
```sql
CREATE TABLE
```
Триггеры на уровне оператора.
```sql
=> CREATE TRIGGER t_before_stmt
BEFORE INSERT OR UPDATE OR DELETE -- события
ON t                              -- таблица
FOR EACH STATEMENT                -- уровень
EXECUTE FUNCTION describe();      -- триггерная функция
```
```sql
CREATE TRIGGER
```
```sql
=> CREATE TRIGGER t_after_stmt
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH STATEMENT EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
И на уровне строк:
```sql
=> CREATE TRIGGER t_before_row
BEFORE INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
```sql
=> CREATE TRIGGER t_after_row
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
Пробуем вставку:
```sql
=> INSERT INTO t VALUES (1,'aaa'), (2, 'bbb');
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE INSERT ROW: (1,aaa)
NOTICE:  t BEFORE INSERT ROW: (2,bbb)
NOTICE:  t AFTER INSERT ROW: (1,aaa)
NOTICE:  t AFTER INSERT ROW: (2,bbb)
NOTICE:  t AFTER INSERT STATEMENT:
INSERT 0 2
```

## Page 11: Page 11 {#source-page-011}

Обновление:
```sql
=> UPDATE t SET s = 'ccc' WHERE id = 1;
```
```sql
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE UPDATE ROW: (1,aaa) -> (1,ccc)
NOTICE:  t AFTER UPDATE ROW: (1,aaa) -> (1,ccc)
NOTICE:  t AFTER UPDATE STATEMENT:
UPDATE 1
```
Триггеры на уровне оператора сработают, даже если команда не обработала ни одной строки:
```sql
=> UPDATE t SET s = 'ddd' WHERE id = 0;
```
```sql
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t AFTER UPDATE STATEMENT:
UPDATE 0
```
Тонкий момент: оператор INSERT с предложением ON CONFLICT приводит к тому, что срабатывают BEFORE-триггеры и на вставку, и на обновление:
```sql
=> INSERT INTO t VALUES (1,'ddd'), (3,'eee')
ON CONFLICT(id) DO UPDATE SET s = EXCLUDED.s;
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE INSERT ROW: (1,ddd)
NOTICE:  t BEFORE UPDATE ROW: (1,ccc) -> (1,ddd)
NOTICE:  t BEFORE INSERT ROW: (3,eee)
NOTICE:  t AFTER UPDATE ROW: (1,ccc) -> (1,ddd)
NOTICE:  t AFTER INSERT ROW: (3,eee)
NOTICE:  t AFTER UPDATE STATEMENT:
NOTICE:  t AFTER INSERT STATEMENT:
INSERT 0 2
```
И, наконец, удаление:
```sql
=> DELETE FROM t WHERE id = 2;
```
```sql
NOTICE:  t BEFORE DELETE STATEMENT:
NOTICE:  t BEFORE DELETE ROW: (2,bbb)
NOTICE:  t AFTER DELETE ROW: (2,bbb)
NOTICE:  t AFTER DELETE STATEMENT:
DELETE 1
```
Для появившегося в PostgreSQL 15 оператора MERGE специального триггера нет, работают триггеры на обновление, удаление и вставку:
```sql
=> MERGE INTO t
USING (VALUES (1, 'fff'), (3, 'ggg'), (4, 'hhh')) AS vals(id, s)
ON t.id = vals.id
WHEN MATCHED AND t.id = 1 THEN
  UPDATE SET s = vals.s
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (id, s)
  VALUES (vals.id, vals.s);
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE DELETE STATEMENT:
NOTICE:  t BEFORE UPDATE ROW: (1,ddd) -> (1,fff)
NOTICE:  t BEFORE DELETE ROW: (3,eee)
NOTICE:  t BEFORE INSERT ROW: (4,hhh)
NOTICE:  t AFTER UPDATE ROW: (1,ddd) -> (1,fff)
NOTICE:  t AFTER DELETE ROW: (3,eee)
NOTICE:  t AFTER INSERT ROW: (4,hhh)
NOTICE:  t AFTER DELETE STATEMENT:
NOTICE:  t AFTER UPDATE STATEMENT:
NOTICE:  t AFTER INSERT STATEMENT:
MERGE 3
```
### Переходные таблицы
Напишем триггерную функцию, показывающую содержимое переходных таблиц. Здесь мы используем имена old\_table и new\_table, которые будут объявлены при создании триггера.

## Page 12: Page 12 {#source-page-012}

Переходные таблицы «выглядят» настоящими, но не присутствуют в системном каталоге и располагаются в оперативной памяти (хотя и могут сбрасываться на диск при большом объеме).
```sql
=> CREATE OR REPLACE FUNCTION transition() RETURNS trigger
AS $$
DECLARE
    rec record;
BEGIN
    IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Старое состояние:';
        FOR rec IN SELECT * FROM old_table LOOP
            RAISE NOTICE '%', rec;
        END LOOP;
    END IF;
    IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
        RAISE NOTICE 'Новое состояние:';
        FOR rec IN SELECT * FROM new_table LOOP
            RAISE NOTICE '%', rec;
        END LOOP;
    END IF;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Создадим новую таблицу:
```sql
=> CREATE TABLE trans(
    id integer PRIMARY KEY,
    n integer
);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO trans VALUES (1,10), (2,20), (3,30);
```
```sql
INSERT 0 3
```
Чтобы при выполнении операции создавались переходные таблицы, необходимо указывать их имена при создании триггера:
```sql
=> CREATE TRIGGER t_after_upd_trans
AFTER UPDATE ON trans -- только одно событие на один триггер
REFERENCING
    OLD TABLE AS old_table
    NEW TABLE AS new_table -- можно и одну, не обязательно обе
FOR EACH STATEMENT
EXECUTE FUNCTION transition();
```
```sql
CREATE TRIGGER
```
Проверим:
```sql
=> UPDATE trans SET n = n + 1 WHERE n <= 20;
```
```sql
NOTICE:  Старое состояние:
NOTICE:  (1,10)
NOTICE:  (2,20)
NOTICE:  Новое состояние:
NOTICE:  (1,11)
NOTICE:  (2,21)
UPDATE 2
```
Переходные таблицы содержат только те строки, которые были затронуты операцией.
Для операций вставки и удаления переходные таблицы работают точно так же, но доступна будет только одна из таблиц: NEW TABLE или OLD TABLE соответственно.
Поскольку триггеры AFTER ROW срабатывают после выполнения всей операции, переходные таблицы можно использовать и в них. Но обычно это не имеет смысла.

## Page 13: Возможное использование {#source-page-013}

  - проверка применимости операции
- BEFORE STATEMENT
- выполнение операции
  - проверка корректности;
  - модификация строки
- BEFORE ROW
  - изменение базовых таблиц представления
- INSTEAD OF ROW
  - проверка согласованности,
  - в том числе на уровне таблицы;
  - «аудит» операций;
  - каскадное изменение таблиц
  - (денормализация, асинхронная
  - обработка...)
- AFTER ROW
- AFTER STATEMENT

### Комментарии преподавателя

Каково практическое применение триггеров?

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

Триггеры BEFORE ROW можно применять для модификации строки (например, заполнить пустое поле нужным значением). Это бывает удобно, чтобы не повторять логику заполнения «технических» полей в каждой операции, а также позволяет вмешаться в работу приложения, код которого недоступен для изменения.

Триггеры INSTEAD OF ROW применяют для того, чтобы отобразить операции над представлением в операции над базовыми таблицами.

Триггеры AFTER ROW и AFTER STATEMENT полезны в случаях, когда нужно знать точное состояние после операции (BEFORE-триггеры могут влиять на результат, так что на этом этапе ясности еще нет):

● для проверки согласованности операции;

● для «аудита», то есть записи изменений в отдельное хранилище;

● для каскадного изменения других таблиц (например, обновлять денормализованные данные при изменении базовых таблиц, или записывать изменения в очередь для последующей обработки вне данной транзакции).

Если операции затрагивают сразу много строк, то триггер AFTER STATEMENT с переходными таблицами может оказаться более эффективным решением, чем триггер AFTER ROW, поскольку позволяет обрабатывать все изменения пакетно, а не построчно.

## Page 14: Сложности {#source-page-014}

**Код вызывается неявно**
  - сложно отследить логику выполнения
- Правила видимости изменчивой триггерной функции
  - виден результат триггеров BEFORE ROW или INSTEAD OF ROW
- Порядок вызова триггеров для одного события
  - триггеры отрабатывают в алфавитном порядке
- Не предотвращается зацикливание
  - триггер может вызвать срабатывание других триггеров
- Можно нарушить ограничения целостности
  - например, исключив из обработки строки, которые должны удалиться

### Комментарии преподавателя

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

В некоторых случаях вместо триггеров можно использовать вычисляемые поля (GENERATED ALWAYS AS ... STORED). Такое решение — если оно подходит — будет заведомо проще и прозрачнее.

Есть ряд тонкостей, связанных с триггерами, которые мы сознательно не рассматриваем подробно:

● правила видимости изменчивых (volatile) функций в триггерах BEFORE ROW и INSTEAD OF ROW (не стоит обращаться к таблице, полагаясь на порядок, в котором сработают триггеры);

● порядок вызова нескольких триггеров, обрабатывающих одно и то же событие (не стоит усугублять и без того неявное срабатывание триггеров завязкой на последовательность обработки);

● возможность зацикливания в случае каскадного срабатывания других триггеров, которые, в свою очередь, могут приводить к новым срабатываниям данного триггера;

● возможность нарушить ограничения целостности (например, при исключении из обработки строки, которая удаляется условием ON DELETE CASCADE, может быть нарушена ссылочная целостность).

Если вы столкнулись с тем, что эти тонкости важны для вашего приложения — серьезно задумайтесь.

## Page 15: Примеры использования триггеров {#source-page-015}

Пример 1: сохранение истории изменения строк.
Пусть есть таблица, содержащая актуальные данные. Задача состоит в том, чтобы в отдельной таблице сохранять всю историю изменения строк основной таблицы.
Поддержку исторической таблицы можно было бы возложить на приложение, но тогда велика вероятность, что в каких-то случаях из-за ошибок история не будет сохраняться. Поэтому решим задачу с помощью триггера.
Основная таблица:
```sql
=> CREATE TABLE coins(
    face_value numeric PRIMARY KEY,
    name text
);
```
```sql
CREATE TABLE
```
Историческая таблица должна называться так же, как основная, но заканчиваться на «\_history». Сначала создаем клон основной таблицы...
```sql
=> CREATE TABLE coins_history(LIKE coins);
```
```sql
CREATE TABLE
```
...и затем добавляем столбцы «действительно с» и «действительно по»:
```sql
=> ALTER TABLE coins_history
    ADD start_date timestamp,
    ADD end_date timestamp;
```
```sql
ALTER TABLE
```
Одна триггерная функция будет вставлять новую историческую строку с открытым интервалом действия:
```sql
=> CREATE OR REPLACE FUNCTION history_insert() RETURNS trigger
AS $$
BEGIN
    EXECUTE format(
        'INSERT INTO %I SELECT ($1).*, current_timestamp, NULL',
        TG_TABLE_NAME||'_history'
    ) USING NEW;
```
```text
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Другая функция будет закрывать интервал действия исторической строки:
```sql
=> CREATE OR REPLACE FUNCTION history_delete() RETURNS trigger
AS $$
BEGIN
    EXECUTE format(
        'UPDATE %I SET end_date = current_timestamp WHERE face_value = $1 AND end_date IS NULL',
        TG_TABLE_NAME||'_history'
    ) USING OLD.face_value;
```
```text
    RETURN OLD;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Теперь создадим триггеры. Важные моменты:
Обновление трактуется как удаление и последующая вставка; здесь важен порядок, в котором сработают триггеры (по алфавиту). Current\_timestamp возвращает время начала транзакции, поэтому при обновлении start\_date одной строки будет равен end\_date другой. Использование AFTER-триггеров позволяет избежать проблем с INSERT ... ON CONFLICT и потенциальными конфликтами с другими триггерами, которые могут существовать на основной таблице.
```sql
=> CREATE TRIGGER coins_history_insert
AFTER INSERT OR UPDATE ON coins
FOR EACH ROW EXECUTE FUNCTION history_insert();
```

## Page 16: Page 16 {#source-page-016}

```sql
CREATE TRIGGER
```
```sql
=> CREATE TRIGGER coins_history_delete
AFTER UPDATE OR DELETE ON coins
FOR EACH ROW EXECUTE FUNCTION history_delete();
```
```sql
CREATE TRIGGER
```
Проверим работу триггеров.
```sql
=> INSERT INTO coins VALUES (0.25, 'Полушка'), (3, 'Алтын');
```
```sql
INSERT 0 2
```
```sql
=> UPDATE coins SET name = '3 копейки' WHERE face_value = 3;
```
```sql
UPDATE 1
```
```sql
=> INSERT INTO coins VALUES (5, '5 копеек');
```
```sql
INSERT 0 1
```
```sql
=> DELETE FROM coins WHERE face_value = 0.25;
```
```sql
DELETE 1
```
```sql
=> SELECT * FROM coins;
```
```text
 face_value |   name
------------+-----------
          3 | 3 копейки
          5 | 5 копеек
(2 rows)
```
В исторической таблице хранится вся история изменений:
```sql
=> SELECT * FROM coins_history ORDER BY face_value, start_date;
```
```text
 face_value |   name    |         start_date         |          end_date
------------+-----------+----------------------------+----------------------------
       0.25 | Полушка   | 2026-04-10 15:40:23.565304 | 2026-04-10 15:40:28.922066
          3 | Алтын     | 2026-04-10 15:40:23.565304 | 2026-04-10 15:40:25.800641
          3 | 3 копейки | 2026-04-10 15:40:25.800641 |
          5 | 5 копеек  | 2026-04-10 15:40:26.861254 |
(4 rows)
```
И теперь по ней можно восстановить состояние на любой момент времени (это немного напоминает работу механизма MVCC). Например, на самое начало:
```text
=> \set d '2026-04-10 15:40:23.755414+03'
```
```sql
=> SELECT face_value, name
FROM coins_history
WHERE start_date <= :'d' AND (end_date IS NULL OR :'d' < end_date)
ORDER BY face_value;
```
```text
 face_value |  name
------------+---------
       0.25 | Полушка
          3 | Алтын
(2 rows)
```
### Примеры использования триггеров
Пример 2: обновляемое представление.
Пусть имеются две таблицы: аэропорты и рейсы:
```sql
=> CREATE TABLE airports(
    code char(3) PRIMARY KEY,
    name text NOT NULL
);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO airports VALUES
    ('SVO', 'Москва. Шереметьево'),
    ('LED', 'Санкт-Петербург. Пулково'),
    ('TOF', 'Томск. Богашево');
```
```sql
INSERT 0 3
```

## Page 17: Page 17 {#source-page-017}

```sql
=> CREATE TABLE flights(
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    airport_from char(3) NOT NULL REFERENCES airports(code),
    airport_to   char(3) NOT NULL REFERENCES airports(code),
    UNIQUE (airport_from, airport_to)
);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO flights(airport_from, airport_to) VALUES
    ('SVO','LED');
```
```sql
INSERT 0 1
```
Для удобства можно определить представление:
```sql
=> CREATE VIEW flights_v AS
SELECT id,
       (SELECT name
        FROM airports
        WHERE code = airport_from) airport_from,
       (SELECT name
        FROM airports
        WHERE code = airport_to) airport_to
FROM flights;
```
```sql
CREATE VIEW
```
```sql
=> SELECT * FROM flights_v;
```
```text
 id |    airport_from     |        airport_to
----+---------------------+--------------------------
  1 | Москва. Шереметьево | Санкт-Петербург. Пулково
(1 row)
```
Но такое представление не допускает изменений. Например, не получится изменить пункт назначения таким образом:
```sql
=> UPDATE flights_v
SET airport_to = 'Томск. Богашево'
WHERE id = 1;
```
```sql
ERROR:  cannot update column "airport_to" of view "flights_v"
DETAIL:  View columns that are not columns of their base relation are not updatable.
```
Однако мы можем определить триггер. Триггерная функция может выглядеть, например, так (для краткости обрабатываем только аэропорт назначения, но не составит труда добавить и аэропорт вылета):
```sql
=> CREATE OR REPLACE FUNCTION flights_v_update() RETURNS trigger
AS $$
DECLARE
    code_to char(3);
BEGIN
    BEGIN
        SELECT code INTO STRICT code_to
        FROM airports
        WHERE name = NEW.airport_to;
    EXCEPTION
        WHEN no_data_found THEN
            RAISE EXCEPTION 'Аэропорт "%" отсутствует', NEW.airport_to;
    END;
    UPDATE flights
    SET airport_to = code_to
    WHERE id = OLD.id; -- изменение id игнорируем
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
И сам триггер:
```sql
=> CREATE TRIGGER flights_v_upd_trigger
INSTEAD OF UPDATE ON flights_v
FOR EACH ROW EXECUTE FUNCTION flights_v_update();
```
```sql
CREATE TRIGGER
```
Проверим:
```sql
=> UPDATE flights_v
SET airport_to = 'Томск. Богашево'
WHERE id = 1;
```

## Page 18: Page 18 {#source-page-018}

```sql
UPDATE 1
```
```sql
=> SELECT * FROM flights_v;
```
```text
 id |    airport_from     |   airport_to
----+---------------------+-----------------
  1 | Москва. Шереметьево | Томск. Богашево
(1 row)
```
Попытка изменить аэропорт на отсутствующий в таблице:
```sql
=> UPDATE flights_v
SET airport_to = 'Южно-Сахалинск. Хомутово'
WHERE id = 1;
```
```text
ERROR:  Аэропорт "Южно-Сахалинск. Хомутово"
отсутствует
CONTEXT:  PL/pgSQL function flights_v_update() line 11 at RAISE
```

## Page 19: Событийные триггеры {#source-page-019}

**Событийный триггер**
  - похож на обычный «табличный» триггер, но другой объект
- Триггерная функция
  - соглашение: функция не принимает параметры,
  - возвращает значение псевдотипа event\_trigger
  - для получения контекста служат специальные функции
- События
  - DDL\_COMMAND\_START
  - перед выполнением команды
  - DDL\_COMMAND\_END
  - после выполнения команды
  - TABLE\_REWRITE
  - перед перезаписью таблицы
  - SQL\_DROP
  - после удаления объектов

### Комментарии преподавателя

Событийные триггеры — по сути те же триггеры, но срабатывают они не на DML-, а на DDL-операции (CREATE, ALTER, DROP, COMMENT, GRANT, REVOKE) и не привязаны к определенному объекту базы данных.

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

<https://postgrespro.ru/docs/postgresql/16/event-trigger-definition>

<https://postgrespro.ru/docs/postgresql/16/event-trigger-matrix>

<https://postgrespro.ru/docs/postgresql/16/sql-createeventtrigger>

<https://postgrespro.ru/docs/postgresql/16/functions-event-triggers>

## Page 20: Триггеры событий {#source-page-020}

Пример триггера для события ddl\_command\_end — завершение DDL-операции.
Создадим функцию, которая описывает контекст вызова:
```sql
=> CREATE OR REPLACE FUNCTION describe_ddl() RETURNS event_trigger
AS $$
DECLARE
    r record;
BEGIN
    -- Для события ddl_command_end контекст вызова в специальной функции
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        RAISE NOTICE '%. тип: %, OID: %, имя: % ',
            r.command_tag, r.object_type, r.objid, r.object_identity;
    END LOOP;
    -- Функции триггера событий не нужно возвращать значение
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Сам триггер:
```sql
=> CREATE EVENT TRIGGER after_ddl
ON ddl_command_end EXECUTE FUNCTION describe_ddl();
```
```sql
CREATE EVENT TRIGGER
```
Создаем новую таблицу:
```sql
=> CREATE TABLE t1(id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY);
```
```sql
NOTICE:  CREATE SEQUENCE. тип: sequence, OID: 16455, имя: public.t1_id_seq
NOTICE:  CREATE TABLE. тип: table, OID: 16456, имя: public.t1
NOTICE:  CREATE INDEX. тип: index, OID: 16459, имя: public.t1_pkey
NOTICE:  ALTER SEQUENCE. тип: sequence, OID: 16455, имя: public.t1_id_seq
CREATE TABLE
```
Создание таблицы может привести к выполнению нескольких команд DDL, поэтому функция pg\_event\_trigger\_ddl\_commands возвращает множество строк.
Триггер на событие table\_rewrite срабатывает до перезаписи таблицы командами ALTER TABLE и ALTER TYPE. Функция ниже выведет информацию о перезаписываемой таблице и код, описывающий причину перезаписи:
```sql
=> CREATE FUNCTION describe_rewrite() RETURNS event_trigger
AS $$
BEGIN
    -- Для события table_rewrite специальные функции возвращают данные:
    RAISE NOTICE 'Будет перезаписана таблица %, код %',
                pg_event_trigger_table_rewrite_oid()::regclass,  -- перезаписываемая таблица
                pg_event_trigger_table_rewrite_reason();  -- код причины перезаписи
END
$$ LANGUAGE plpgsql;
```
```sql
NOTICE:  CREATE FUNCTION. тип: function, OID: 16461, имя: public.describe_rewrite()
CREATE FUNCTION
```
Теперь создаем триггер:
```sql
=> CREATE EVENT TRIGGER before_rewrite
ON table_rewrite EXECUTE FUNCTION describe_rewrite();
```
```sql
CREATE EVENT TRIGGER
```
Изменим таблицу — назначим другой тип столбцу id:
```sql
=> ALTER TABLE t1 ALTER COLUMN id type bigint;
```
```sql
NOTICE:  Будет перезаписана таблица t1, код 4
NOTICE:  ALTER SEQUENCE. тип: sequence, OID: 16455, имя: public.t1_id_seq
NOTICE:  ALTER TABLE. тип: table, OID: 16456, имя: public.t1
ALTER TABLE
```
Хотя перезапись таблицы может быть вызвана и другими командами, в частности CLUSTER и VACUUM FULL, событие table\_rewrite для них не вызывается.

## Page 21: Page 21 {#source-page-021}


## Page 22: Итоги {#source-page-022}

**Триггер — способ отреагировать на возникновение события**
- С помощью триггера можно отменить операцию, изменить ее результат или выполнить дополнительные действия
- Триггер выполняется как часть транзакции; ошибка в триггере приводит к откату транзакции
- Использование триггеров AFTER ROW и переходных таблиц удорожает обработку Все хорошо в меру: сложную логику трудно отлаживать из-за неявного выполнения триггеров

## Page 23: Практика {#source-page-023}

1. Создайте триггер, обрабатывающий обновление поля onhand\_qty представления catalog\_v.
- Проверьте, что в «Каталоге» появилась возможность заказывать книги. 2. Обеспечьте выполнение требования согласованности: количество книг на складе не может быть отрицательным (нельзя купить книгу, которой нет в наличии).
  - Внимательно проверьте правильность реализации, учитывая,
  - что с приложением могут одновременно работать несколько
  - пользователей.

### Комментарии преподавателя

2. Может показаться, что достаточно создать AFTER-триггер на таблице operations, подсчитывающий сумму qty\_change. Однако на уровне изоляции Read Committed, с которым работает приложение «Книжный магазин», нам придется блокировать таблицу operations в эксклюзивном режиме — иначе возможны сценарии, при которых такая проверка не сработает.

Лучше поступить следующим образом: добавить в таблицу books поле onhand\_qty и создать триггер, изменяющий это поле при изменении таблицы operations (то есть, фактически, выполнить денормализацию данных). На поле onhand\_qty теперь можно наложить ограничение CHECK, реализующее требование согласованности. А функция onhand\_qty(), которую мы создавали ранее, больше не нужна.

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

## Page 24: 1. Триггер для обновления каталога {#source-page-024}

```sql
=> CREATE FUNCTION update_catalog() RETURNS trigger
AS $$
BEGIN
    INSERT INTO operations(book_id, qty_change) VALUES
        (OLD.book_id, NEW.onhand_qty - coalesce(OLD.onhand_qty,0));
    RETURN NEW;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER update_catalog_trigger
INSTEAD OF UPDATE ON catalog_v
FOR EACH ROW
EXECUTE FUNCTION update_catalog();
```
```sql
CREATE TRIGGER
```
### 2. Проверка количества книг
Добавляем к таблице книг поле наличного количества. (До версии 11 важно было учитывать, что указание предложения DEFAULT вызывало перезапись всех строк таблицы, удерживая блокировку.)
```sql
=> ALTER TABLE books ADD COLUMN onhand_qty integer;
```
```sql
ALTER TABLE
```
Триггерная функция для AFTER-триггера на вставку для обновления количества (предполагаем, что поле onhand\_qty не может быть пустым):
```sql
=> CREATE FUNCTION update_onhand_qty() RETURNS trigger
AS $$
BEGIN
    UPDATE books
    SET onhand_qty = onhand_qty + NEW.qty_change
    WHERE book_id = NEW.book_id;
    RETURN NULL;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Дальше все происходит внутри транзакции.
```text
=> BEGIN;
```
```text
BEGIN
```
Блокируем операции на время транзакции:
```text
=> LOCK TABLE operations;
```
```text
LOCK TABLE
```
Начальное заполнение:
```sql
=> UPDATE books b
SET onhand_qty = (
    SELECT coalesce(sum(qty_change),0)
    FROM operations o
    WHERE o.book_id = b.book_id
);
```
```sql
UPDATE 6
```
Теперь, когда поле заполнено, задаем ограничения:
```sql
=> ALTER TABLE books ALTER COLUMN onhand_qty SET DEFAULT 0;
```
```sql
ALTER TABLE
```
```sql
=> ALTER TABLE books ALTER COLUMN onhand_qty SET NOT NULL;
```
```sql
ALTER TABLE
```
```sql
=> ALTER TABLE books ADD CHECK(onhand_qty >= 0);
```

## Page 25: Page 25 {#source-page-025}

```sql
ALTER TABLE
```
Создаем триггер:
```sql
=> CREATE TRIGGER update_onhand_qty_trigger
AFTER INSERT ON operations
FOR EACH ROW
EXECUTE FUNCTION update_onhand_qty();
```
```sql
CREATE TRIGGER
```
Готово.
```text
=> COMMIT;
```
```text
COMMIT
```
Теперь books.onhand\_qty обновляется, но представление catalog\_v по-прежнему вызывает функцию для подсчета количества. Хоть в исходном запросе обращение к функции синтаксически не отличается от обращения к полю, запрос был запомнен в другом виде:
```sql
=> \d+ catalog_v
```
```sql
                            View "bookstore.catalog_v"
    Column    |  Type   | Collation | Nullable | Default | Storage  | Description
--------------+---------+-----------+----------+---------+----------+-------------
 book_id      | integer |           |          |         | plain    |
 title        | text    |           |          |         | extended |
 onhand_qty   | integer |           |          |         | plain    |
 display_name | text    |           |          |         | extended |
 authors      | text    |           |          |         | extended |
View definition:
 SELECT book_id,
    title,
    onhand_qty(b.*) AS onhand_qty,
    book_name(book_id, title) AS display_name,
    authors(b.*) AS authors
   FROM books b
  ORDER BY (book_name(book_id, title));
Triggers:
    update_catalog_trigger INSTEAD OF UPDATE ON catalog_v FOR EACH ROW EXECUTE FUNCTION
update_catalog()
```
Поэтому пересоздадим представление:
```sql
=> CREATE OR REPLACE VIEW catalog_v AS
SELECT b.book_id,
       b.title,
       b.onhand_qty,
       book_name(b.book_id, b.title) AS display_name,
       b.authors
FROM   books b
ORDER BY display_name;
```
```sql
CREATE VIEW
```
Теперь функцию можно удалить.
```sql
=> DROP FUNCTION onhand_qty(books);
```
```sql
DROP FUNCTION
```
Небольшая проверка:
```sql
=> SELECT * FROM catalog_v WHERE book_id = 1 \gx
```
```text
-[ RECORD 1 ]+------------------------------------
book_id      | 1
title        | Сказка о царе Салтане
onhand_qty   | 19
display_name | Сказка о царе Салтане. Пушкин А. С.
authors      | Пушкин Александр Сергеевич
```
```sql
=> INSERT INTO operations(book_id, qty_change) VALUES (1,+10);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM catalog_v WHERE book_id = 1 \gx
```

## Page 26: Page 26 {#source-page-026}

```text
-[ RECORD 1 ]+------------------------------------
book_id      | 1
title        | Сказка о царе Салтане
onhand_qty   | 29
display_name | Сказка о царе Салтане. Пушкин А. С.
authors      | Пушкин Александр Сергеевич
```
Некорректные операции обрываются:
```sql
=> INSERT INTO operations(book_id, qty_change) VALUES (1,-100);
```
```sql
ERROR:  new row for relation "books" violates check constraint "books_onhand_qty_check"
DETAIL:  Failing row contains (1, Сказка о царе Салтане, -71).
CONTEXT:  SQL statement "UPDATE books
    SET onhand_qty = onhand_qty + NEW.qty_change
    WHERE book_id = NEW.book_id"
PL/pgSQL function update_onhand_qty() line 3 at SQL statement
```

## Page 27: Практика+ {#source-page-027}

1. Напишите триггер, увеличивающий счетчик (поле version) на единицу при каждом изменении строки. При вставке новой строки счетчик должен устанавливаться в единицу. Проверьте правильность работы. 2. Даны таблицы заказов (orders) и строк заказов (lines). Требуется выполнить денормализацию: автоматически обновлять сумму заказа в таблице orders при изменении строк в заказе.
  - Создайте необходимые триггеры с использованием
  - переходных таблиц для минимизации операций обновления.

### Комментарии преподавателя

2. Для создания таблиц используйте команды:

```sql
CREATE TABLE orders (
    id int PRIMARY KEY,
    total_amount numeric(20,2) NOT NULL DEFAULT 0
);
```

```sql
CREATE TABLE lines (
   id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   order_id int NOT NULL REFERENCES orders(id),
   amount numeric(20,2) NOT NULL
);
```

Столбец orders.total\_amount должен автоматически вычисляться как сумма значений столбца lines.amount всех строк, относящихся к соответствующему заказу.

## Page 28: 1. Счетчик номера версии {#source-page-028}

```sql
=> CREATE DATABASE plpgsql_triggers;
```
```sql
CREATE DATABASE
```
```sql
=> \c plpgsql_triggers
```
```text
You are now connected to database "plpgsql_triggers" as user "student".
```
Таблица:
```sql
=> CREATE TABLE t(
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    s text,
    version integer
);
```
```sql
CREATE TABLE
```
Триггерная функция:
```sql
=> CREATE FUNCTION inc_version() RETURNS trigger
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.version := 1;
    ELSE
        NEW.version := OLD.version + 1;
    END IF;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Триггер:
```sql
=> CREATE TRIGGER t_inc_version
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE FUNCTION inc_version();
```
```sql
CREATE TRIGGER
```
Проверяем:
```sql
=> INSERT INTO t(s) VALUES ('Раз');
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | Раз |       1
(1 row)
```
Явное указание version игнорируется:
```sql
=> INSERT INTO t(s,version) VALUES ('Два',42);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | Раз |       1
  2 | Два |       1
(2 rows)
```
Изменение:
```sql
=> UPDATE t SET s = lower(s) WHERE id = 1;
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM t;
```

## Page 29: Page 29 {#source-page-029}

```text
 id |  s  | version
----+-----+---------
  2 | Два |       1
  1 | раз |       2
(2 rows)
```
Явное указание также игнорируется:
```sql
=> UPDATE t SET s = lower(s), version = 42 WHERE id = 2;
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | раз |       2
  2 | два |       2
(2 rows)
```
### 2. Автоматическое вычисление общей суммы заказов
Создаем таблицы упрощенной структуры, достаточной для демонстрации:
```sql
=> CREATE TABLE orders (
    id integer PRIMARY KEY,
    total_amount numeric(20,2) NOT NULL DEFAULT 0
);
```
```sql
CREATE TABLE
```
```sql
=> CREATE TABLE lines (
   id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   order_id integer NOT NULL REFERENCES orders(id),
   amount numeric(20,2) NOT NULL
);
```
```sql
CREATE TABLE
```
Создаем триггерную функцию и триггер для обработки вставки:
```sql
=> CREATE FUNCTION total_amount_ins() RETURNS trigger
AS $$
BEGIN
    WITH l(order_id, total_amount) AS (
        SELECT order_id, sum(amount)
        FROM new_table
        GROUP BY order_id
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Предложение FROM в команде UPDATE позволяет соединить orders с подзапросом по переходной таблице и использовать столбцы подзапроса для вычисления значения.
```sql
=> CREATE TRIGGER lines_total_amount_ins
AFTER INSERT ON lines
REFERENCING
    NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_ins();
```
```sql
CREATE TRIGGER
```
Функция и триггер для обработки обновления:

## Page 30: Page 30 {#source-page-030}

```sql
=> CREATE FUNCTION total_amount_upd() RETURNS trigger
AS $$
BEGIN
    WITH l_tmp(order_id, amount) AS (
        SELECT order_id, amount FROM new_table
        UNION ALL
        SELECT order_id, -amount FROM old_table
    ), l(order_id, total_amount) AS (
        SELECT order_id, sum(amount)
        FROM l_tmp
        GROUP BY order_id
        HAVING sum(amount) <> 0
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Условие HAVING позволяет пропускать изменения, не влияющие на общую сумму заказа.
```sql
=> CREATE TRIGGER lines_total_amount_upd
AFTER UPDATE ON lines
REFERENCING
    OLD TABLE AS old_table
    NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_upd();
```
```sql
CREATE TRIGGER
```
Функция и триггер для обработки удаления:
```sql
=> CREATE FUNCTION total_amount_del() RETURNS trigger
AS $$
BEGIN
    WITH l(order_id, total_amount) AS (
        SELECT order_id, -sum(amount)
        FROM old_table
        GROUP BY order_id
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER lines_total_amount_del
AFTER DELETE ON lines
REFERENCING
    OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_del();
```
```sql
CREATE TRIGGER
```
Остался неохваченным оператор TRUNCATE. Однако триггер для этого оператора не может использовать переходные таблицы. Но мы знаем, что после выполнения TRUNCATE в lines не останется строк, значит можно обнулить суммы всех заказов.
```sql
=> CREATE FUNCTION total_amount_truncate() RETURNS trigger
AS $$
BEGIN
    UPDATE orders SET total_amount = 0;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER lines_total_amount_truncate
AFTER TRUNCATE ON lines
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_truncate();
```

## Page 31: Page 31 {#source-page-031}

```sql
CREATE TRIGGER
```
Дополнительно нужно запретить изменять значение total\_amount вручную, но это задача решается не триггерами.
Проверяем работу.
Добавили два новых заказа без строк:
```sql
=> INSERT INTO orders VALUES (1), (2);
```
```sql
INSERT 0 2
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |         0.00
  2 |         0.00
(2 rows)
```
Добавили строки в заказы:
```sql
=> INSERT INTO lines (order_id, amount) VALUES
    (1,100), (1,100), (2,500), (2,500);
```
```sql
INSERT 0 4
```
```sql
=> SELECT * FROM lines;
```
```text
 id | order_id | amount
----+----------+--------
  1 |        1 | 100.00
  2 |        1 | 100.00
  3 |        2 | 500.00
  4 |        2 | 500.00
(4 rows)
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       200.00
  2 |      1000.00
(2 rows)
```
Удвоили суммы всех строк всех заказов:
```sql
=> UPDATE lines SET amount = amount * 2;
```
```sql
UPDATE 4
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       400.00
  2 |      2000.00
(2 rows)
```
Удалим одну строку первого заказа:
```sql
=> DELETE FROM lines WHERE id = 1;
```
```sql
DELETE 1
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       200.00
  2 |      2000.00
(2 rows)
```
Опустошим таблицу строк:
```text
=> TRUNCATE lines;
```
```text
TRUNCATE TABLE
```
```sql
=> SELECT * FROM orders ORDER BY id;
```

## Page 32: Page 32 {#source-page-032}

```text
 id | total_amount
----+--------------
  1 |         0.00
  2 |         0.00
(2 rows)
```
```sql
=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
=> DROP DATABASE plpgsql_triggers;
```
```sql
DROP DATABASE
```