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

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;