Создание триггеров для добавления данных в таблицу аудита

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

ID, Server_Name, User_Name, Date_Time, Table_Name, Column_Name, Old_Value, New_Value

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

Спасибо и всего наилучшего, Партха

Источник

2 ответа

Решение

Я могу предоставить вам своего рода алгоритм для работы, большая часть основной работы уже выполнена:

Это может быть ваша таблица аудита, следует добавить столбец отметки времени в качестве измененной даты или дополнительную информацию в соответствии с вашими требованиями:

CREATE TABLE audit (
     old_data VARCHAR(100),
     new_data VARCHAR(100),
     tbl_name VARCHAR(100)
)
|

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

CREATE TRIGGER testtrigger BEFORE UPDATE ON <table_name>
  FOR EACH ROW BEGIN
    INSERT INTO audit(old_data, new_data, tbl_name) VALUES (OLD.first_name, NEW.first_name, "testtable");
  END;
|

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

IF(OLD.column_name <> NEW.column_name) THEN
    --Your insert query here
ELSE
    --NOOP
END IF;

Дайте знать, если требуется дополнительная информация.

Источник

Вы можете использовать этот триггер, но если он для каждой таблицы для меня, то он лучший, потому что вы контролируете, если что-то изменяется в структуре таблицы и не влияет на другие, вы можете использовать пример этого репо: https://github.com/areliszxz/mysql_audit

DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`update`
BEFORE UPDATE ON `tudbaauditar`.`tutablaaauditar` #aqui puedes poner antes o despues del update
FOR EACH ROW
BEGIN
        /*Paso de variables para un mejor control*/
        set @res1 = ''; set @res2 = ''; set @res3 = ''; set @res4 = '';
        /*Sacamos info de la ip donde se ejecuta la accion de UPDATE*/
        select host as IP INTO @ipcl from information_schema.processlist WHERE ID=connection_id();
        #concatenamos los campos de la tabla a auditar y verificamos que no sean null, en caso de que los campos sean null agregamos un espacio
        #las variables (new,old)son de mysql, el valor old es el que ya se tenia en la tabla y el new es el valor que se modifico

        #Valores viejos
        SET @oldq = CONCAT (' id ',ifnull(OLD.id,''),
                                                        ' campo1 ',ifnull(OLD.campo1,''),
                                                        ' campo2 ',ifnull(OLD.campo2,''),
                                                        ' campo3 ',ifnull(OLD.campo3,''));
        #Valores nuevos
        SET @newq = CONCAT (' id ',ifnull(new.id,''),
                                                        ' campo1 ',ifnull(new.campo1,''),
                                                        ' campo2 ',ifnull(new.campo2,''),
                                                        ' campo3 ',ifnull(new.campo3,''));
    #guardamos en una variable los valores que unicamente cambiaron                                                 
    IF OLD.id <> new.id THEN set @res1 = CONCAT ('Cambio id ',ifnull(OLD.id,''), ' a: ',ifnull(new.id,'')); END IF;
    IF OLD.campo1 <> new.campo1 THEN set @res2 = CONCAT ('Cambio campo1 ',ifnull(OLD.campo1,''), ' a: ',ifnull(new.campo1,'')); END IF;
    IF OLD.campo2 <> new.campo2 THEN set @res3 = CONCAT ('Cambio campo2 ',ifnull(OLD.campo2,''), ' a: ',ifnull(new.campo2,'')); END IF;
    IF OLD.campo3 <> new.campo3 THEN set @res4 = CONCAT ('Cambio campo3 ',ifnull(OLD.campo3,''), ' a: ',ifnull(new.campo3,'')); END IF;
    SET @resC=CONCAT(ifnull(@res1,''),'|',ifnull(@res2,''),'|',ifnull(@res3,''),'|',ifnull(@res4,''));

    #insertamos en nuestra tabla de log la informacion
    INSERT INTO basedeauditoria.tablalogs (old,new,usuario,typo,fecha,tabla,valor_alterado,ip)                
    VALUES (@oldq ,@newq,CURRENT_USER,"UPDATE",NOW(),"tutablaaauditar",ifnull(@resC,'No cambio nada'),@ipcl);
END$$

#log de insertados(Nuevos registros)
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`incert`
BEFORE INSERT ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
    SET @oldq = '';
    SET @newq = CONCAT (' id ',ifnull(new.id,''),
    ' campo1 ',ifnull(new.campo1,''),
    ' campo2 ',ifnull(new.campo2,''),
    ' campo3 ',ifnull(new.campo3,''));
    INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)                
    VALUES (@oldq ,@newq,CURRENT_USER,"INSERT",NOW(),"tutablaaauditar");
END$$

#log de Borrados
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`delete`
AFTER DELETE ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
    SET @newq = '';
    SET @oldq = CONCAT (' id ',ifnull(new.id,''),
    ' campo1 ',ifnull(new.campo1,''),
    ' campo2 ',ifnull(new.campo2,''),
    ' campo3 ',ifnull(new.campo3,''));
    INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)                
    VALUES (@oldq ,@newq,CURRENT_USER,"DELETE",NOW(),"tutablaaauditar");
END$$
Источник

Это немного улучшенная версия ответа Влада. В таблице аудита есть столбец для различий изменений.

Правила аудита:

  • INSERT и DELETE - полная запись со всеми полями сохраняется в таблице аудита
  • UPDATE - сохраняются только изменения по полям

Структура таблицы аудита. diff столбец, в котором хранятся изменения.

NB: privileges - это столбец JSON в таблице, которую мы проверяем в этом случае.

CREATE TABLE roles_audit_log (
    roles_id INT UNSIGNED NOT NULL,
    diff JSON,
    dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    dml_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dml_created_by VARCHAR(255) NOT NULL default 'system',
    PRIMARY KEY (roles_id, dml_type, dml_timestamp)
);

Триггер для UPDATEзаявление. Заполнит diff столбец с полями, которые были изменены.

DELIMITER $$

CREATE TRIGGER roles_audit_au AFTER UPDATE ON `roles` FOR EACH ROW
BEGIN
    DECLARE m_change text;
    SET m_change = JSON_OBJECT();

    IF coalesce(NEW.role, '') != coalesce(OLD.role, '') THEN
        SET m_change = JSON_SET(m_change, '$.role', NEW.role);
    END IF;

    IF coalesce(NEW.created_at, '') != coalesce(OLD.created_at, '') THEN
        SET m_change = JSON_SET(m_change, '$.created_at', NEW.created_at);
    END IF;

    IF coalesce(NEW.updated_at, '') != coalesce(OLD.updated_at, '') THEN
        SET m_change = JSON_SET(m_change, '$.updated_at', NEW.updated_at);
    END IF;

    IF coalesce(NEW.privileges, '') != coalesce(OLD.privileges, '') THEN
        SET m_change = JSON_SET(m_change, '$.privileges', NEW.privileges);
    END IF;

    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        NEW.id,
        m_change,
        'UPDATE',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;

Триггер для DELETEзаявление. Заполнит diff с полной удаленной строкой.

DELIMITER $$

CREATE TRIGGER roles_audit_ad AFTER DELETE ON `roles` FOR EACH ROW
BEGIN
    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        OLD.id,
        JSON_OBJECT(
            'id', OLD.id,
            'role', OLD.role,
            'privileges', OLD.privileges,
            'created_at', OLD.created_at,
            'updated_at', OLD.updated_at
        ),
        'DELETE',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;

Триггер для INSERTзаявление. Заполнит diff с полной удаленной строкой.

DELIMITER $$

CREATE TRIGGER roles_audit_ai AFTER INSERT ON `roles` FOR EACH ROW
BEGIN
    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        NEW.id,
        JSON_OBJECT(
            'id', NEW.id,
            'role', NEW.role,
            'privileges', NEW.privileges,
            'created_at', NEW.created_at,
            'updated_at', NEW.updated_at
        ),
        'INSERT',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;
Источник

Я потратил несколько дней, чтобы придумать хранимую процедуру для автоматического / динамического создания триггеров UPDATE / DELETE в MariaDB (работает с v 10.1.9), проверяя все изменения обновлений и удалений. Решение использует INFORMATION_SCHEMA для автоматического создания триггера аудита для каждой из ваших таблиц. При обновлении проверяются только измененные столбцы, а при удалении вся история сохраняется в аудите.

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

    -- Dynamic Automated Update / Delete Triggers in MariaDB
    -- Leonard Tonna 19/05/2016 - www.ilabmalta.com

    CREATE DATABASE db_ilabmalta_test;

    USE db_ilabmalta_test;

    CREATE TABLE tb_auditDetail(
        audit_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        type varchar(1) NOT NULL,
        tablename varchar(128) NULL,
        pk varchar(128) NULL,
        fieldname varchar(128) NULL,
        oldvalue varchar(1000) NULL,
        newvalue varchar(1000) NULL,
        updatedate datetime NULL,
        username varchar(128) NULL,
        dbusername varchar(128) NULL,
        machinename varchar(128) NULL);

    CREATE TABLE tb_company(
        cmp_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        cmp_name varchar(100) NOT NULL,
        cmp_no varchar(16) NULL,
        cmp_status smallint NOT NULL DEFAULT 1,
        cmp_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        cmp_createdby varchar(10) NOT NULL,
        cmp_updated datetime NULL,
        cmp_updatedby varchar(10) NULL,
        cmp_record_version int(9) NOT NULL DEFAULT 1 ) ;

    -- We now create sp_maketrigger which is the stored procedure
    -- which will give us our trigger scripts

    DELIMITER $$

    DROP PROCEDURE IF EXISTS sp_maketrigger; 

    CREATE PROCEDURE sp_maketrigger (IN s_tablename CHAR(30), OUT u_trigger_out VARCHAR(65500) CHARACTER SET ascii,OUT d_trigger_out VARCHAR(65500) CHARACTER SET ascii)
    BEGIN
        DECLARE s_fieldname VARCHAR(50);
        DECLARE u_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE d_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE s_key VARCHAR(50);
        DECLARE s_updatedby VARCHAR(50);
        DECLARE s_updated VARCHAR(50);
        DECLARE s_recversion VARCHAR(50);
        DECLARE done INT DEFAULT 0; 
        DECLARE cursor_end CONDITION FOR SQLSTATE '02000'; 
        DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw;
        DECLARE pri_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw2;
        DECLARE upd_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw3;
        DECLARE rec_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw4;
        DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1; 

        DROP VIEW IF EXISTS test_prepare_vw; 
        DROP VIEW IF EXISTS test_prepare_vw2; 
        DROP VIEW IF EXISTS test_prepare_vw3; 
        DROP VIEW IF EXISTS test_prepare_vw4; 

        SET u_trigger = '';
        SET u_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_update;\n');
        SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',s_tablename,'_update AFTER UPDATE ON ',s_tablename,' FOR EACH ROW \n');
        SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
        SET u_trigger = CONCAT(u_trigger,'DECLARE msg VARCHAR(255); \n');

        SET d_trigger = '';
        SET d_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_delete;\n');
        SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',s_tablename,'_delete AFTER DELETE ON ',s_tablename,' FOR EACH ROW \n');
        SET d_trigger = CONCAT(d_trigger,'BEGIN \n');

        SET @query = CONCAT('CREATE VIEW test_prepare_vw2 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME NOT LIKE \'%updated%\' AND COLUMN_KEY = \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN pri_cursor;
        FETCH pri_cursor INTO s_key; 
        CLOSE pri_cursor; 
        DROP VIEW test_prepare_vw2; 

        SET @query = CONCAT('CREATE VIEW test_prepare_vw3 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%updatedby%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN upd_cursor;
        FETCH upd_cursor INTO s_updatedby; 
        CLOSE upd_cursor; 
        DROP VIEW test_prepare_vw3; 
        SET s_updated = LEFT(s_updatedby,(LENGTH(RTRIM(s_updatedby)))-2);

        SET @query = CONCAT('CREATE VIEW test_prepare_vw4 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%record_version%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN rec_cursor;
        FETCH rec_cursor INTO s_recversion; 
        CLOSE rec_cursor; 
        DROP VIEW test_prepare_vw4; 

        SET @query = CONCAT('CREATE VIEW test_prepare_vw as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        SET u_trigger = CONCAT(u_trigger,'   IF (ISNULL(NEW.',s_recversion,') OR OLD.',s_recversion,' >= NEW.',s_recversion,' OR ISNULL(NEW.',s_updatedby,') OR NEW.',s_updatedby,' = \'\' OR ISNULL(NEW.',s_updated,') OR NEW.',s_updated,' = OLD.',s_updated,') THEN \n');
        SET u_trigger = CONCAT(u_trigger,'      set msg = \'Cannot update record without specifying updated/updatedby by columns and without incrementing the record version.\'; \n');
        SET u_trigger = CONCAT(u_trigger,'      SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = msg; \n');
        SET u_trigger = CONCAT(u_trigger,'   END IF;     \n');

        OPEN col_cursor;

        FETCH col_cursor INTO s_fieldname; 
        WHILE done = 0 DO 
            SET u_trigger = CONCAT(u_trigger,'   IF (IFNULL(OLD.',s_fieldname,',\'\') <> IFNULL(NEW.',s_fieldname,',\'\') ) THEN\n');
            SET u_trigger = CONCAT(u_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET u_trigger = CONCAT(u_trigger,'     VALUES (\'U\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,', NEW.',s_fieldname,', CURRENT_TIMESTAMP,NEW.',s_updatedby,',CURRENT_USER(),@@hostname);\n');
            SET u_trigger = CONCAT(u_trigger,'   END IF;\n'); 

            SET d_trigger = CONCAT(d_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET d_trigger = CONCAT(d_trigger,'     VALUES (\'D\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,',NULL, CURRENT_TIMESTAMP,NULL,CURRENT_USER(),@@hostname);\n');

            FETCH col_cursor INTO s_fieldname; 
        END WHILE; 
        CLOSE col_cursor; 

        DROP VIEW test_prepare_vw; 

        SET u_trigger = CONCAT(u_trigger,'END;$$ \nDELIMITER ; \n');
        SET d_trigger = CONCAT(d_trigger,'END;$$ \nDELIMITER ; \n');
        SELECT u_trigger INTO u_trigger_out;
        SELECT d_trigger INTO d_trigger_out;


    END; $$

    DELIMITER ;

    -- And finally, to extract the Trigger Scripts

    call sp_maketrigger('tb_company',@s_line1,@d_line1);

    SELECT CONCAT(@s_line1,@d_line1)

    -- You just need to copy, paste and execute the trigger script, and
    -- voila, your audit is in place.

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

Однако вы можете настроить хранимую процедуру sp_maketrigger по-разному в соответствии с вашими потребностями. SP также подлежит улучшениям и улучшениям.

Источник

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

Таблицы базы данных

Предположим, у нас есть библиотечное приложение со следующими двумя таблицами:

Лучший способ сохранить старое и новое состояние строки - использовать столбцы 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  |

Это оно!

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

Источник
Другие вопросы по тегам