How to create audit trail or logging tables with triggers in MySQL

Question

I want a trigger that triggers whenever the loan table is updated (i.e. a book is returned). It should take values from the rows in the loan table only where the loan is overdue and insert them into a new table.

---

the 'loan' table:

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

and the 'overdue' table

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

---

What I've got so far:

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 ;

---

I'm getting "an error in (my) SQL syntax" on the END IF and I have no clue why. Any help will be much appreciated!

Answer 1

Storing the old and new row state in JSON

The best way to store the old and new row state is to use JSON columns. SO, for each table that you want to enable audit logging, you can create an audit log table, like this one:

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

• The book_id column stores the identifier of the book row that has been either created, updated, or deleted.
• The old_row_data is a JSON column that will capture the state of the book record prior to executing an INSERT, UPDATE, or DELETE statement.
• The new_row_data is a JSON column that will capture the state of the book record after executing an INSERT, UPDATE, or DELETE statement.
• The dml_type is an enumeration column that stores the DML statement type that created, updated, or deleted a given book record.
• The dml_timestamp stores the DML statement execution timestamp.
• The dml_created_by stores the application user who issued the INSERT, UPDATE, or DELETE DML statement.

Intercepting INSERT, UPDATE, and DELETE DML statements using triggers

Now, to feed the audit log tables, you need to create the following 3 triggers:

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

The JSON_OBJECT MySQL function allows us to create a JSON object that takes the provided key-value pairs.

The dml_type column is set to the value of INSERT, UPDATE or DELETE and the dml_timestamp value is set to the CURRENT_TIMESTAMP.

The dml_created_by column is set to the value of the @logged_user MySQL session variable, which was previously set by the application with the currently logged user:

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()
            )
        )
    );
});

Testing time

When executing an INSERT statement on the book table:

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

We can see that a record is inserted in the book_audit_log that captures the INSERT statement that was just executed on the book table:

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

When updating the book table row:

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

We can see that a new record is going to be added to the book_audit_log by the AFTER UPDATE trigger on the book table:

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

When deleting the book table row:

DELETE FROM book 
WHERE id = 1

A new record is added to the book_audit_log by the AFTER DELETE trigger on the book table:

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

That's it!

Answer 2

I created a tool called cdc_audit that automates creation of audit tables in mysql for any or all tables, and even preserves pre-existing triggers. Maybe you or someone will find it useful

Features

• automates generation of audit tables
• automates generation of triggers to populate audit tables
• automates syncing of new rows in audit tables to .csv files.
• Reads mysql information_schema to automatically determine tables and columns.
• Can generate tables + triggers for all database tables, or a specified list.
• Can sync audit tables for all database tables, or a specified list.
• Retains pre-existing trigger logic, if any, when generating AFTER triggers.
• sync script option to delete all but last audit row, to keep source DB small.

Update: here's an example, using the loan table above in a test database named stackoverflow.

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

Successfully Generated Audit Tables + Triggers in ./cdc_audit_gen

Now let's run the sql to create audit table plus triggers in the DB.

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

that's it. Audit table plus triggers are in place.

If curious, we can examine the implementation.

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

Answer 3

Try this, You are missing semicolon in your syntax and delimiter

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 ;

Answer 4

I understand i am too late for the party... nevertheless the below code i am using will help to create Audit trial on any database on any table name.

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