mysql 触发器_MySQL通过触发器（Triggers）生成审计日志（Audit Logging）

审计日志（Audit Logging），也就是用户对数据库的各种活动的记录, 如新增、删除和更新等。很多时候，我们不仅需要知道最终的数据，还需要了解数据更新的记录，比如我们想知道什么时候，什么时间，某某对哪个表单的某某字段进行了什么修改。

下文将简要介绍如何通过触发器（Triggers）生成审计日志，以图书馆的书籍管理为例。

书目表单book结构如下

1. id bigint(20)
2. author varchar(255)
3. price_in_cent int(11)
4. publisher varchar(255)
5. title varchar(255)

首先创建审计日志表单 book_audit_log

CREATE 

其次分别生成新增、删除和更新触发器

（1）新增记录触发器 book_insert_audit_trigger

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(
        Domain NEW.id is, probably, for sale,
        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

（2）更新记录触发器 book_update_audit_trigger

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

(3) 删除记录触发器 book_delete_audit_trigger

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

最后通过测试查看一下触发器的效果。

（1）新增1条记录

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)结果

| 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  |

（2）更新记录

UPDATE book
SET price_in_cents = 4499
WHERE id = 1

审计日志(book_audit_log)结果

| 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  |

(3) 删除记录

DELETE FROM book
WHERE id = 1

审计日志(book_audit_log)结果

| 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  |

效果不错！！！

参考：

（1）https://vladmihalcea.com/mysql-audit-logging-triggers/

（2）Is there a MySQL option/feature to track history of changes to records?