Создание контрольного журнала с триггером в MySQL

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

таблица ссуд:

CREATE TABLE loan (
    book_code INT NOT NULL, 
    student_num INT NOT NULL, 
    out_date DATE NOT NULL, 
    due_date DATE NOT NULL, 
    return_date DATE, 
    CONSTRAINT pk_loan PRIMARY KEY (book_code, student_num, out_date),
    CONSTRAINT fk_book_code FOREIGN KEY (book_code) REFERENCES copy(book_code),
    CONSTRAINT fk_num FOREIGN KEY (student_num) REFERENCES student(student_num)
);

и "просроченная" таблица

CREATE TABLE overdue (
    overdue_id INT NOT NULL AUTO_INCREMENT,
    student_num INT NOT NULL, 
    out_date DATE NOT NULL, 
    due_date DATE NOT NULL, 
    return_date DATE,
    CONSTRAINT pk_overdue PRIMARY KEY (overdue_id),
    CONSTRAINT fk_num FOREIGN KEY (student_num) REFERENCES student(student_num)
 );

Что у меня так далеко:

DELIMITER $$

CREATE TRIGGER trg_overdue_loans AFTER UPDATE ON loan FOR EACH ROW
    BEGIN   
        IF (NEW.return_date > OLD.due_date) THEN 
            INSERT INTO overdue (student_num, out_date, due_date, return_date)
            VALUES (OLD.student_num, OLD.out_date, OLD.due_date, NEW.return_date)
        END IF;
    END$$

DELIMITER ;

Я получаю сообщение об ошибке в (моем) синтаксисе SQL на END IF и я понятия не имею, почему. Любая помощь будет высоко ценится!

Источник

2 ответа

Решение

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

DROP TRIGGER IF EXISTS trg_overdue_loans;
DELIMITER $$    
CREATE TRIGGER `trg_overdue_loans` AFTER UPDATE ON loan FOR EACH ROW
    BEGIN   
        IF NEW.return_date > OLD.due_date THEN 
         INSERT INTO overdue (student_num, out_date, due_date, return_date)
         VALUES (OLD.student_num, OLD.out_date, OLD.due_date, NEW.return_date);
        END IF;
    END;$$

DELIMITER ;
Источник

Поскольку это очень распространенное требование, следующий ответ основан на статье, которую я написал в своем блоге.

Сохранение состояния старой и новой строки в JSON

Лучший способ сохранить старое и новое состояние строки - использовать столбцы JSON. Итак, для каждой таблицы, в которой вы хотите включить ведение журнала аудита, вы можете создать таблицу журнала аудита, например эту:

CREATE TABLE book_audit_log (
    book_id BIGINT NOT NULL, 
    old_row_data JSON,
    new_row_data JSON,
    dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    dml_timestamp TIMESTAMP NOT NULL,
    dml_created_by VARCHAR(255) NOT NULL,
    PRIMARY KEY (book_id, dml_type, dml_timestamp)
)
  • В book_id столбец хранит идентификатор book строка, которая была создана, обновлена ​​или удалена.
  • В old_row_data столбец JSON, который фиксирует состояние book запись перед выполнением оператора INSERT, UPDATE или DELETE.
  • В new_row_data столбец JSON, который фиксирует состояние book запись после выполнения оператора INSERT, UPDATE или DELETE.
  • В dml_type это столбец перечисления, в котором хранится тип оператора DML, который создал, обновил или удалил данный book запись.
  • В dml_timestamp хранит метку времени выполнения оператора DML.
  • В dml_created_by хранит пользователя приложения, который выполнил оператор INSERT, UPDATE или DELETE DML.

Перехват инструкций INSERT, UPDATE и DELETE DML с помощью триггеров

Теперь, чтобы заполнить таблицы журнала аудита, вам необходимо создать следующие 3 триггера:

CREATE TRIGGER book_insert_audit_trigger
AFTER INSERT ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        null,
        JSON_OBJECT(
            "title", NEW.title,
            "author", NEW.author,
            "price_in_cents", NEW.price_in_cents,
            "publisher", NEW.publisher
        ),
        'INSERT',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

CREATE TRIGGER book_update_audit_trigger
AFTER UPDATE ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        JSON_OBJECT(
            "title", OLD.title,
            "author", OLD.author,
            "price_in_cents", OLD.price_in_cents,
            "publisher", OLD.publisher
        ),
        JSON_OBJECT(
            "title", NEW.title,
            "author", NEW.author,
            "price_in_cents", NEW.price_in_cents,
            "publisher", NEW.publisher
        ),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

CREATE TRIGGER book_delete_audit_trigger
AFTER DELETE ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        OLD.id,
        JSON_OBJECT(
            "title", OLD.title,
            "author", OLD.author,
            "price_in_cents", OLD.price_in_cents,
            "publisher", OLD.publisher
        ),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

В JSON_OBJECT Функция MySQL позволяет нам создать объект JSON, который принимает предоставленные пары ключ-значение.

В dml_type столбец установлен на значение INSERT, UPDATE или DELETE и dml_timestamp значение установлено на CURRENT_TIMESTAMP.

В dml_created_by столбец установлен на значение @logged_user Переменная сеанса MySQL, которая была ранее установлена ​​приложением с текущим зарегистрированным пользователем:

Session session = entityManager.unwrap(Session.class);

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices()
    .getDialect();

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "SET @logged_user = '%s'", 
            ReflectionUtils.invokeMethod(
                dialect,
                "escapeLiteral",
                LoggedUser.get()
            )
        )
    );
});

Время тестирования

При выполнении инструкции INSERT на book Таблица:

INSERT INTO book (
    id,
    author, 
    price_in_cents, 
    publisher, 
    title
) 
VALUES (
    1,
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition'
)

Мы видим, что запись вставлена ​​в book_audit_log который захватывает оператор INSERT, который только что был выполнен на book Таблица:

| book_id | old_row_data | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |              | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |

При обновлении book строка таблицы:

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

Мы видим, что новая запись будет добавлена ​​в book_audit_log триггером AFTER UPDATE на book Таблица:

| book_id | old_row_data                                                                                                                         | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |                                                                                                                                      | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-07-29 13:50:48 | Vlad Mihalcea  |

При удалении book строка таблицы:

DELETE FROM book 
WHERE id = 1

В список добавляется новая запись. book_audit_log триггером AFTER DELETE на book Таблица:

| book_id | old_row_data                                                                                                                         | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |                                                                                                                                      | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-07-29 13:50:48 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                      | DELETE   | 2020-07-29 14:05:33 | Vlad Mihalcea  |

Это оно!

Для получения дополнительной информации по этой теме ознакомьтесь с этой статьей в моем блоге.

Источник

Я создал инструмент с именем cdc_audit, который автоматизирует создание таблиц аудита в mysql для любой или всех таблиц и даже сохраняет уже существующие триггеры. Может быть, вы или кто-то найдет это полезным

Характеристики

  • автоматизирует генерацию таблиц аудита
  • автоматизирует генерацию триггеров для заполнения таблиц аудита
  • автоматизирует синхронизацию новых строк в таблицах аудита с CSV-файлами.
  • Читает mysql information_schema для автоматического определения таблиц и столбцов.
  • Может генерировать таблицы + триггеры для всех таблиц базы данных или указанного списка.
  • Может синхронизировать таблицы аудита для всех таблиц базы данных или указанного списка.
  • Сохраняет существующую логику триггера, если таковая имеется, при генерации триггеров AFTER.
  • опция сценария синхронизации для удаления всех строк, кроме последней, для сохранения размера исходной БД.

Обновление: вот пример, использующий таблицу ссуд выше в тестовой базе данных с именем stackru.

$ ./cdc_audit_gen_mysql.php -t loan -d stackru

Successfully Generated Audit Tables + Triggers in ./cdc_audit_gen

Теперь давайте запустим sql для создания таблицы аудита плюс триггеры в БД.

$ mysql -u root stackru < cdc_audit_gen/loan.audit.sql

вот и все. Таблица аудита плюс триггеры на месте.

Если любопытно, мы можем изучить реализацию.

$ cat cdc_audit_gen/loan.audit.sql 


/**
 * Audit table for table (loan).
 *
 * !!! DO NOT MODIFY THIS FILE MANUALLY !!!
 *
 * This file is auto-generated and is NOT intended
 * for manual modifications/extensions.
 *
 * For additional documentation, see:
 * https://github.com/dan-da/cdc_audit
 *
 */
create table if not exists `loan_audit` (
  `book_code` int(11) not null    comment 'Primary key in source table loan',
  `student_num` int(11) not null    comment 'Primary key in source table loan',
  `out_date` date not null    comment 'Primary key in source table loan',
  `due_date` date not null    comment '',
  `return_date` date null    comment '',
  `audit_event` enum('insert','update','delete') not null    comment 'Indicates event that occurred in source table',
  `audit_timestamp` timestamp not null    comment 'Updated when record is inserted, updated or deleted in source table',
  `audit_pk` int(11) not null  primary key auto_increment comment 'Audit table primary key, useful for sorting since mysql time data types are only granular to second level.',
   index (`book_code`, `student_num`, `out_date`),
   index (`audit_timestamp`)
);

/**
 * Audit triggers for table (loan).
 *
 * For additional documentation, see:
 * https://github.com/dan-da/cdc_audit
 *
 */

-- loan after INSERT trigger.
DELIMITER @@
CREATE TRIGGER `loan_after_insert` AFTER INSERT ON `loan`
 FOR EACH ROW BEGIN
  insert into `loan_audit` (`book_code`, `student_num`, `out_date`, `due_date`, `return_date`, `audit_event`, `audit_timestamp`) values(NEW.`book_code`, NEW.`student_num`, NEW.`out_date`, NEW.`due_date`, NEW.`return_date`, 'insert', CURRENT_TIMESTAMP);


 END;
@@

-- loan after UPDATE trigger.      
DELIMITER @@
CREATE TRIGGER `loan_after_update` AFTER UPDATE ON `loan`
 FOR EACH ROW BEGIN
  insert into `loan_audit` (`book_code`, `student_num`, `out_date`, `due_date`, `return_date`, `audit_event`, `audit_timestamp`) values(NEW.`book_code`, NEW.`student_num`, NEW.`out_date`, NEW.`due_date`, NEW.`return_date`, 'update', CURRENT_TIMESTAMP);


 END;
@@

-- loan after DELETE trigger.
DELIMITER @@
CREATE TRIGGER `loan_after_delete` AFTER DELETE ON `loan`
 FOR EACH ROW BEGIN
  insert into `loan_audit` (`book_code`, `student_num`, `out_date`, `due_date`, `return_date`, `audit_event`, `audit_timestamp`) values(OLD.`book_code`, OLD.`student_num`, OLD.`out_date`, OLD.`due_date`, OLD.`return_date`, 'delete', CURRENT_TIMESTAMP);


 END;
Источник

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

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "sample_schema_name";

SET @tableName = "sample_table_name";



SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` timestamp DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
    where table_name = @tableName
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
Источник
Другие вопросы по тегам