Журналирование действий с данными

К этому моменту была разработана система пополнения демонстрационной БД SALES данными, пришедшими из внешних источников в формате JSON. Однако, для финансовых систем характерно обязательное наличие средств учета и контроля полученных записей. Например, для записей о продажах желательно иметь учет того, какие записи были добавлены и какие записи были изменены (что именно и когда). Стандартным решением является ведение журнала действий с записями.

В данном случае простейшей БД требуется журнал действий с 2 таблицами:

• SALE_RECORDS – факт продажи
• ITEMS – сумма продажи и количество товаров

БД нормализована, следовательно, эти таблицы имеют разную структуру и хранят разную информацию. Важные для учета общие части информации – это:

• дата-время изменения
• номер транзакции

Остальные поля - отличаются, но можно собрать всю информацию в строку в формате JSON и поместить в одно поле единственной таблицы. Для удобства фильтрации можно добавить поле с названием учитываемой таблицы.

Таким образом, для задачи учета и ведения журнала изменений требуется добавить еще одну таблицу

CREATE TABLE SALES_LOG (  
 ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1),  
 TRANS_ID BIGINT NOT NULL,  
 TRANS_TIME TIMESTAMP NOT NULL,  
 TABLE_NAME VARCHAR(255) CHARACTER SET UTF8 NOT NULL,  
 DATA VARCHAR(255));

с полями:

имя	хранимые данные
ID	первичный ключ
TRANS_ID	номер транзакции
TRANS_TIME	точное время события
TABLE_NAME	имя таблицы для учета
DATA	нужные данные в формате JSON

Какие именно данные нужны для журнала?

1. Кто и когда сделал изменения. Номер транзакции и время позволяют получить полную информацию для ответа на этот вопрос
2. Для таблицы SALE_RECORDS (факт продажи) полезно сохранить для учета ссылку на запись о покупателе, ID записи SREC_ID, дату продажи и ID внешней записи, чтобы понимать, какие записи из входного потока не попали в БД.
3. Кроме того, желательно сохранить начальные данные о количестве видов товара, количестве каждого и общей сумме данной записи о продаже. Эти данные попадают в таблицу ITEMS и их требуется вычислять путем агрегации.
4. При изменении записи желательно сохранить информацию о действии и значении полей после него.
5. Фиксируется факт удаления записи и значения полей в этот момент.

Для демонстрации концепции решения и его быстрой реализации были рассмотрены два варианта.

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

Можно воспользоваться триггером на вставку, изменение или удаление записи из таблицы SALE_RECORDS, чтобы сформировать JSON-строку с нужной информацией и вставить запись в таблицу SALES_LOG. Этот способ хорошо сработает при учете модификации или удаления записей SALE_RECORDS. Однако, на момент вставки новой записи в эту таблицу еще будут отсутствовать связанные записи в таблице ITEMS. Из-за этого для учета новых записей был применен второй способ.

Вставка новых записей в таблицу SALE_RECORDS происходит в результате импорта из данных в формате JSON. Разбор JSON и выделение информации из него происходит в процедуре IMPORT_SALE_JSON. В процессе разбора JSON-строки процедуре доступны записи из массива ITEMS для каждой записи о продаже. Разумным решением было бы включение кода для сохранения информации в журнал прямо в текст этой процедуры.

Были сделаны следующие дополнения в код процедуры:

1. в строках 20..22 определены новые переменные

   20 DECLARE it_cnt int;  
   21 DECLARE it_sum decimal(16,2);  
   22 DECLARE LMODE varchar(20);

2. которые были проинициализированы в строках 90, 96, 111, 112

   
   90   LMODE = 'APPEND';
   ...
   96   LMODE = 'INSERT';
   ...

111 it_cnt = 0;
112 it_sum = 0;

3. в строках 143, 144 выполняется подсчет сумм и агрегация данных из массива `items` в эти переменные
```sql
 :it_cnt = :it_cnt + 1;  
 :it_sum = :it_sum + (:quantity * :price);

4. Наконец, начиная с 155 строки выполняется сохранение всей необходимой информации в таблицу SALES_LOG в формате JSON. Для этого применяется оператор JSON_OBJECT из стандарта SQL/JSON РЕД База Данных.

   IF (:it_cnt > 0) THEN  
   BEGIN  
   insert into SALES_LOG(TRANS_ID, TRANS_TIME, TABLE_NAME, "DATA")  
    select  
    CURRENT_TRANSACTION, localtimestamp(0), 'SALE_RECORDS',  
    JSON_OBJECT(  
    'trans_id': current_transaction,  
    'local': localtimestamp(0),  
    'table': 'SALE_RECORDS',  
    'mode' value :LMODE,  
    'customer' value :cust_id,  
    'oid' value :sale_oid,  
    'saleid' value :srec_id,  
    'saledate' value cast( left( replace(:sale_date,'T',' '),char_length(:sale_date)-1) as timestamp),  
    'items' value :it_cnt,  
    'sum' value :it_sum  
    returning varchar(254)  
    )  
    from RDB$DATABASE;   
   END

В результате в таблицу SALES_LOG попадают записи вида

 select first 5 * from SALES_LOG;

Для учета других событий служит триггер.

Примеры отчетов по загрузке

/* число загрузок = транзакций */
select count(distinct trans_ID) from sales_log;
/* 
count = 2
*/

/* число записей в каждой загрузке */
select trans_ID, count(*) from sales_log group by trans_id;
/*
TRANS_ID COUNT
-------- -----
10349     5000
10357        1
*/

Итоги

В данном решении задачи учета и контроля данных демонстрационной БД SALES было показано, насколько полезны другие функции стандартного SQL/JSON СУБД РЕД База данных, в частности, функции генерации. JSON давно стал одним из основных форматов обмена данными, и функции генерации SQL/JSON значительно облегчают решение подобных задач.

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

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

Дата последнего изменения: 11.09.2026

Если вы нашли ошибку, пожалуйста, выделите текст и нажмите Ctrl+Enter.