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 BOOLEAN,
    NEW_BESTSELLER BOOLEAN,
    PRIMARY KEY (`EVENT_ID`)
);

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

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

create trigger BOOKS_UPDATE AFTER update on BOOKS
for each row
begin
	insert into BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER,
							OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER)
		values (curtime(), "UPDATE", OLD.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER,
							OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER);
end$$

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

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

create trigger READERS_UPDATE AFTER update 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,
							OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESELID, OLD_VIP_LEVEL)
		values (curtime(), "UPDATE", OLD.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL,
							OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL);
end$$

DELIMITER ;