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

Методы реализации отслеживания изменений:

1. Отслеживание изменений на основе временных меток.
   Этот метод предполагает добавление в таблицу столбца временных меток для записи времени последнего изменения. Вы можете использовать эту метку времени, чтобы идентифицировать изменения, внесенные с определенного момента времени.

   Пример: добавление столбца временной метки «last_modified» в таблицу с помощью SQL:

   ALTER TABLE your_table ADD COLUMN last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

2. Отслеживание изменений на основе триггеров.
   Триггеры — это объекты базы данных, которые можно использовать для автоматического выполнения кода при возникновении определенного события. Создав триггеры для операций вставки, обновления и удаления в таблице, вы можете фиксировать внесенные изменения и сохранять их в отдельной таблице отслеживания.

   Пример: создание таблицы «audit_table» для отслеживания изменений с помощью триггера в PostgreSQL:

   CREATE TABLE audit_table (
      id SERIAL PRIMARY KEY,
      table_name TEXT,
      operation TEXT,
      timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   CREATE OR REPLACE FUNCTION track_changes()
   RETURNS TRIGGER AS $$
   BEGIN
      IF (TG_OP = 'INSERT') THEN
          INSERT INTO audit_table (table_name, operation) VALUES (TG_TABLE_NAME, 'INSERT');
      ELSIF (TG_OP = 'UPDATE') THEN
          INSERT INTO audit_table (table_name, operation) VALUES (TG_TABLE_NAME, 'UPDATE');
      ELSIF (TG_OP = 'DELETE') THEN
          INSERT INTO audit_table (table_name, operation) VALUES (TG_TABLE_NAME, 'DELETE');
      END IF;
      RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   CREATE TRIGGER track_changes_trigger
   AFTER INSERT OR UPDATE OR DELETE ON your_table
   FOR EACH ROW EXECUTE FUNCTION track_changes();

3. Отслеживание измененных данных (CDC).
   CDC — это функция, предоставляемая некоторыми системами управления базами данных, которая фиксирует и записывает изменения, внесенные в таблицы, в режиме реального времени. Для точного отслеживания изменений обычно используются журналы транзакций или репликация базы данных.

   Пример: включение CDC для таблицы SQL Server:

   -- Enable CDC on the database
   EXEC sys.sp_cdc_enable_db;
   -- Enable CDC on the specific table
   EXEC sys.sp_cdc_enable_table
      @source_schema = 'dbo',
      @source_name = 'your_table',
      @role_name = NULL;

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

   Пример: создание исторической таблицы в MySQL:

   CREATE TABLE your_table_history LIKE your_table;
   ALTER TABLE your_table_history
      ADD COLUMN version_id INT AUTO_INCREMENT,
      ADD COLUMN modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      ADD COLUMN modified_by VARCHAR(255);
   INSERT INTO your_table_history SELECT *, NULL, NULL FROM your_table;