Cacher is the code snippet organizer for pro developers

CREATE TABLE BOOKS_AUD ( 
	EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
    EVENT_DATE DATETIME NOT NULL,
    EVENT_TYPE VARCHAR(10) DEFAULT NULL,
    BOOK_ID INT(11) NOT NULL, 
    OLD_TITLE VARCHAR(255),
    NEW_TITLE VARCHAR(255),
    OLD_PUBYEAR INT(4),
    NEW_PUBYEAR INT(4),
    OLD_BESTSELLER TINYINT(1),
    NEW_BESTSELLER TINYINT(1),
    PRIMARY KEY (`EVENT_ID`)
);

COMMIT

DELIMITER $$

CREATE TRIGGER BOOKS_INSERT AFTER INSERT ON BOOKS
FOR EACH ROW
BEGIN
	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
			VALUES (CURTIME(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
END $$

DELIMITER ;

INSERT INTO BOOKS (TITLE, PUBYEAR)
		VALUES ('TEST', 2000);
        
COMMIT ;

DELIMITER $$

CREATE TRIGGER BOOKS_DELETE AFTER DELETE ON BOOKS
FOR EACH ROW
BEGIN
	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID)
		VALUES (CURTIME(), "DELETE", OLD.BOOK_ID);
END $$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER BOOKS_UPDATE AFTER UPDATE ON BOOKS
FOR EACH ROW
BEGIN
	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, OLD_TITLE, NEW_TITLE, 
												OLD_PUBYEAR, NEW_PUBYEAR, OLD_BESTSELLER, NEW_BESTSELLER)
							VALUES (CURTIME(), "UPDATE", OLD.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
END $$

DELIMITER ;

CREATE TABLE READERS_AUD ( 
	EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
    EVENT_DATE DATETIME NOT NULL,
    EVENT_TYPE VARCHAR(10) DEFAULT NULL,
    READER_ID INT(11) NOT NULL, 
    OLD_FIRSTNAME VARCHAR(255),
    NEW_FIRSTNAME VARCHAR(255),
    OLD_LASTNAME VARCHAR(255),
    NEW_LASTNAME VARCHAR(255),
    OLD_PESELID VARCHAR(11),
    NEW_PESELID VARCHAR(11),
    OLD_VIP_LEVEL VARCHAR(20),
    NEW_VIP_LEVEL VARCHAR(20),
    PRIMARY KEY (`EVENT_ID`)
);

DELIMITER $$

CREATE TRIGGER READERS_INSERT AFTER INSERT ON READERS
FOR EACH ROW
BEGIN
	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL)
			VALUES (CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
END $$

DELIMITER ;

INSERT INTO READERS (FIRSTNAME, LASTNAME, PESELID) VALUES ('TESTNAME', 'TESTLASTNAME', 'PESEL');

COMMIT;

SELECT * FROM READERS_AUD;

DELIMITER $$

CREATE TRIGGER READERS_DELETE AFTER DELETE ON READERS
FOR EACH ROW
BEGIN
	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID)
		VALUES (CURTIME(), "DELETE", OLD.READER_ID);
END $$

DELIMITER ;

DELETE FROM READERS WHERE READER_ID = 6;

COMMIT;

DELIMITER $$

CREATE TRIGGER READERS_UPDATE AFTER UPDATE ON READERS
FOR EACH ROW
BEGIN
	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, OLD_FIRSTNAME, NEW_FIRSTNAME, 
												OLD_LASTNAME, NEW_LASTNAME, OLD_PESELID, NEW_PESELID, OLD_VIP_LEVEL, NEW_VIP_LEVEL)
                                                
							VALUES (CURTIME(), "UPDATE", OLD.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
END $$

DELIMITER ;

UPDATE READERS SET FIRSTNAME = 'TEST'
	WHERE READER_ID = 1;

COMMIT;