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

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 ;