Автоматическое вычисление общей суммы заказов

@Whitecolor · Регистрация: 10.03.2012

Студворк — интернет-сервис помощи студентам

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

SQL
1
2
3
4
CREATE TABLE orders (
id INT PRIMARY KEY,
total_amount NUMERIC(20,2) NOT NULL DEFAULT 0
);

SQL
1
2
3
4
5
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
);

@Swa111 · 11.07.2025, 18:12

А что такое переходные таблицы?

@Whitecolor · 11.07.2025, 19:08 **[ТС]**

Таблица, указанная при создании триггера как OLD TABLE, содержит старые значения строк, обработанных триггером, а таблица NEW TABLE - новые значения тех же строк.

@Swa111 · 11.07.2025, 19:35

хм, действительно интересный подход.

Идея

При вставке/обновлении/удалении строк в lines нам нужно обновить сумму по соответствующим order_id.
Используем триггеры AFTER INSERT OR UPDATE OR DELETE с переходными таблицами OLD_TABLE и NEW_TABLE (PostgreSQL 10+).
Собираем уникальные order_id из затронутых строк (учитывая старые и новые значения).
Для каждого такого order_id пересчитываем сумму по lines и обновляем orders.total_amount.

Создание функции триггера

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE FUNCTION trg_lines_update_order_total()
RETURNS TRIGGER AS $$
DECLARE
    affected_orders INT[];
BEGIN
    -- Собираем уникальные order_id из OLD и NEW переходных таблиц
    SELECT ARRAY(
        SELECT DISTINCT order_id FROM (
            SELECT order_id FROM OLD_TABLE
            UNION
            SELECT order_id FROM NEW_TABLE
        ) t
        WHERE order_id IS NOT NULL
    ) INTO affected_orders;
 
    -- Обновляем total_amount для каждого затронутого заказа
    UPDATE orders o
    SET total_amount = amount
    FROM
     (SELECT idOrder , COALESCE(SUM(amount),0) amount 
      FROM 
        unnest(affected_orders) idOrder 
        LEFT JOIN LINES ON LINES.order_id = idOrder  GROUP BY idOrder 
     ) t
    WHERE o.id = t.idOrder AND total_amount IS DISTINCT FROM amount;
 
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Создание триггера на таблицу `lines`

SQL
1
2
3
4
5
CREATE TRIGGER trg_lines_after_change
AFTER INSERT OR UPDATE OR DELETE ON LINES
REFERENCING OLD TABLE AS OLD_TABLE NEW TABLE AS NEW_TABLE
FOR EACH STATEMENT
EXECUTE FUNCTION trg_lines_update_order_total();

Объяснение

REFERENCING OLD TABLE AS OLD_TABLE NEW TABLE AS NEW_TABLE позволяет получить все старые и новые строки, затронутые оператором.
FOR EACH STATEMENT — триггер срабатывает один раз на оператор, а не на каждую строку.
В функции собираются все уникальные order_id, которые были в старых и новых строках.
Для каждого такого заказа пересчитывается сумма по строкам.
Используется COALESCE на случай, если строк для заказа нет (тогда сумма 0).

@Sandys · 13.07.2025, 12:42

SQL
1
2
AFTER INSERT OR UPDATE OR DELETE ON LINES
REFERENCING OLD TABLE AS OLD_TABLE NEW TABLE AS NEW_TABLE

Так нельзя делать в PostgreSQL. Будет ошибка "ERROR: transition tables cannot be specified for triggers with more than one event"
При использовании transition tables необходимо создавать триггер на каждое событие!

@Аватар · 13.07.2025, 13:54

зачем вообще денормализация для элементарно считающейся в запросе общей суммы заказа? разве что для учебы

@Sandys · 13.07.2025, 17:13

Сообщение от Аватар

зачем вообще денормализация для элементарно считающейся в запросе общей суммы заказа? разве что для учебы

В данном случае денормализация не нужна абсолютно, но если топикстартеру так хочется заморочиться, то можно, например, так:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
-- Удаляем таблицы, если они существуют
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS LINES CASCADE;
 
-- Создаем таблицу заказов
CREATE TABLE orders (
    id INT PRIMARY KEY,
    total_amount NUMERIC(20,2) NOT NULL DEFAULT 0
);
 
-- Создаем таблицу строк заказа
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
);
 
-- Удаляем старые триггеры и функции, если они существуют
DROP TRIGGER IF EXISTS trg_order_total_insert ON LINES;
DROP TRIGGER IF EXISTS trg_order_total_update ON LINES;
DROP TRIGGER IF EXISTS trg_order_total_delete ON LINES;
 
DROP FUNCTION IF EXISTS order_total_adjust(INT, NUMERIC);
DROP FUNCTION IF EXISTS order_total_insert();
DROP FUNCTION IF EXISTS order_total_update();
DROP FUNCTION IF EXISTS order_total_delete();
 
-- Функция для безопасного обновления суммы заказа
-- Блокирует заказ, проверяет отрицательный баланс и корректирует сумму
CREATE OR REPLACE FUNCTION order_total_adjust(p_order_id INT, p_delta NUMERIC)
RETURNS VOID AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Блокируем строку заказа
    SELECT total_amount INTO current_balance
    FROM orders
    WHERE id = p_order_id
    FOR UPDATE;
 
    -- Проверяем, найден ли заказ
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Заказ с ID % не найден', p_order_id
            USING HINT = FORMAT('Операция невозможна: заказ должен существовать');
    END IF;
 
    -- Проверяем, не станет ли сумма отрицательной
    IF current_balance + p_delta < 0 THEN
        RAISE EXCEPTION 'Операция приведет к отрицательной сумме заказа (order_id = %)', p_order_id
            USING HINT = FORMAT('Изменение: %, текущий баланс: %', p_delta, current_balance);
    END IF;
 
    -- Обновляем сумму заказа
    UPDATE orders
    SET total_amount = total_amount + p_delta
    WHERE id = p_order_id;
 
    RETURN;
 
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Ошибка при обновлении total_amount: %', SQLERRM;
        RAISE;
END;
$$ LANGUAGE plpgsql;
 
COMMENT ON FUNCTION order_total_adjust (INT, NUMERIC) IS 'Обновляет total_amount в заказе с проверкой блокировки и отрицательного значения';
 
-- Функция для обработки новых строк заказа
-- Суммирует новые значения и добавляет их в общий заказ
CREATE OR REPLACE FUNCTION order_total_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- Для каждого заказа добавляем сумму новых строк
    PERFORM order_total_adjust(d.order_id, d.sum_amount)
    FROM (
        SELECT order_id, SUM(amount) AS sum_amount
        FROM new_table
        GROUP BY order_id
    ) d;
 
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Триггер AFTER INSERT
CREATE TRIGGER trg_order_total_insert
AFTER INSERT ON LINES
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION order_total_insert();
 
COMMENT ON TRIGGER trg_order_total_insert ON LINES IS 'Триггер AFTER INSERT: автоматически обновляет total_amount в orders';
 
-- Функция для обработки обновления строк заказа
-- Обрабатывает изменения суммы, перемещение между заказами и т.д.
CREATE OR REPLACE FUNCTION order_total_update()
RETURNS TRIGGER AS $$
BEGIN
    -- Обновляем суммы заказов, учитывая:
    -- 1. Изменение суммы в том же заказе
    -- 2. Перемещение строки из одного заказа в другой
    PERFORM order_total_adjust(order_id, SUM(delta))
    FROM (
        -- Изменение суммы в том же заказе
        SELECT o.order_id, (n.amount - o.amount) AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id = n.order_id AND o.amount <> n.amount
 
        UNION ALL
 
        -- Удаление строки из старого заказа
        SELECT o.order_id, -o.amount AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id <> n.order_id
 
        UNION ALL
 
        -- Добавление строки в новый заказ
        SELECT n.order_id, n.amount AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id <> n.order_id
    ) AS changes
    GROUP BY order_id;
 
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Триггер AFTER UPDATE
CREATE TRIGGER trg_order_total_update
AFTER UPDATE ON LINES
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION order_total_update();
 
COMMENT ON TRIGGER trg_order_total_update ON LINES IS 'Триггер AFTER UPDATE: автоматически обновляет total_amount в orders';
 
-- Функция для обработки удаления строк заказа
-- Вычитает сумму удалённых строк из общего заказа
CREATE OR REPLACE FUNCTION order_total_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- Для каждого заказа вычитаем сумму удалённых строк
    PERFORM order_total_adjust(d.order_id, -d.total_deleted)
    FROM (
        SELECT order_id, SUM(amount) AS total_deleted
        FROM old_table
        GROUP BY order_id
    ) d;
 
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Триггер AFTER DELETE
CREATE TRIGGER trg_order_total_delete
AFTER DELETE ON LINES
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION order_total_delete();
 
COMMENT ON TRIGGER trg_order_total_delete ON LINES IS 'Триггер AFTER DELETE: автоматически обновляет total_amount в orders';
 
-- Покрывающий индекс для ускорения запросов по order_id и amount
CREATE INDEX idx_lines_order_id_amount ON LINES(order_id, amount);

@Swa111 · 14.07.2025, 08:31

Не по теме:

Сообщение от Аватар

зачем вообще денормализация для элементарно считающейся в запросе общей суммы заказа? разве что для учебы

Все очень просто - для скорости. Взять готовую сумму - это допустим 2 мс. А посчитать сумму строк по ведомости где их несколько тысяч может доходить до 500мс. А теперь допустим нужно вывести реестр ведомостей допустим из 100 шт... а пользователи нервные бывают :)

Добавлено через 10 минут

Сообщение от Sandys

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Изменение суммы в том же заказе
        SELECT o.order_id, (n.amount - o.amount) AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id = n.order_id AND o.amount <> n.amount
UNION ALL
-- Удаление строки из старого заказа
        SELECT o.order_id, -o.amount AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id <> n.order_id
UNION ALL
-- Добавление строки в новый заказ
        SELECT n.order_id, n.amount AS delta
        FROM old_table o
        JOIN new_table n USING (id)
        WHERE o.order_id <> n.order_id

А зачем так сложно? оно и в приницпе не будет так работать, например в случае обновления всех строк одной ведомости

наверно можно так:

SQL
1
2
3
4
5
6
7
        SELECT order_id, - amount delta
        FROM old_table
 
        UNION ALL
 
        SELECT order_id, amount
        FROM new_table

@Sandys · 16.07.2025, 18:38

Сообщение от Swa111

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

Эта инструкция позволяет обработать все измененные строки за раз

SQL
1
2
3
AFTER UPDATE ON LINES
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT

Сообщение от Swa111

А зачем так сложно?

Такая логика для того, чтобы:

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

Сообщение от Swa111

наверно можно так:

Можно, но не надо :-)

@Whitecolor · 17.07.2025, 14:26 **[ТС]**

SQL
1
2
3
4
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
total_amount NUMERIC(20,2) NOT NULL DEFAULT 0
);

SQL
1
2
3
4
5
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
1
2
3
4
5
6
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
1
2
3
4
5
6
7
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
1
2
3
4
5
6
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
1
2
3
4
5
6
7
CREATE FUNCTION total_amount_truncate() RETURNS TRIGGER 
AS $$ 
BEGIN 
    UPDATE orders SET total_amount = 0;
    RETURN NULL;
END 
$$ LANGUAGE plpgsql;

SQL
1
2
3
4
CREATE TRIGGER lines_total_amount_truncate 
AFTER TRUNCATE ON LINES 
FOR EACH STATEMENT 
EXECUTE FUNCTION total_amount_truncate();

SQL
1
2
INSERT INTO orders VALUES (1), (2);
SELECT * FROM orders ORDER BY id;

SQL
1
2
3
4
INSERT INTO LINES (order_id, amount) VALUES
(1,100), (1,100), (2,500), (2,500);
SELECT * FROM LINES;
SELECT * FROM orders ORDER BY id;

SQL
1
2
UPDATE LINES SET amount = amount * 2;
SELECT * FROM orders ORDER BY id;

SQL
1
2
DELETE FROM LINES WHERE id = 1;
SELECT * FROM orders ORDER BY id;

SQL
1
2
TRUNCATE LINES;
SELECT * FROM orders ORDER BY id;

Новые блоги и статьи Все статьи Все блоги /
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Как объединить две одинаковые БД Access с разными данными VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .	Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .

@Swa111 912 / 286 / 57 Регистрация: 01.06.2023 Сообщений: 807
	11.07.2025, 18:12
	А что такое переходные таблицы? 0

@Whitecolor 1184 / 754 / 127 Регистрация: 10.03.2012 Сообщений: 4,847
	11.07.2025, 19:08 [ТС]
	Таблица, указанная при создании триггера как `OLD TABLE`, содержит старые значения строк, обработанных триггером, а таблица `NEW TABLE` - новые значения тех же строк. 0

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	13.07.2025, 13:54
	зачем вообще денормализация для элементарно считающейся в запросе общей суммы заказа? разве что для учебы 0

Автоматическое вычисление общей суммы заказов

Идея

Создание функции триггера

Создание триггера на таблицу lines

Объяснение

Создание триггера на таблицу `lines`