1. CREATE TABLE READERS_AUD (
2.         EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
3.     EVENT_DATE DATETIME NOT NULL,
4.     EVENT_TYPE VARCHAR(10) DEFAULT NULL,
5.     READER_ID INT (11) NOT NULL,
6.     OLD_FIRSTNAME VARCHAR(255),
7.     NEW_FIRSTNAME VARCHAR(255),
8.     OLD_LASTNAME VARCHAR(255),
9.     NEW_LASTNAME VARCHAR(255),
10.     OLD_PESELID VARCHAR(11),
11.     NEW_PESELID VARCHAR(11),
12.     OLD_VIP_LEVEL VARCHAR(20),
13.     NEW_VIP_LEVEL VARCHAR(20),
14.     PRIMARY KEY(EVENT_ID)
15.     );
16.    
17. DELIMITER $$
18.  
19. CREATE TRIGGER READERS_UPDATE AFTER UPDATE ON READERS
20. FOR EACH ROW
21. BEGIN
22.         INSERT INTO READERS_AUD(EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME,
23.                                                         NEW_PESELID, NEW_VIP_LEVEL, OLD_FIRSTNAME, OLD_LASTNAME,
24.                                                         OLD_PESELID,OLD_VIP_LEVEL)
25.                 VALUES(CURTIME(), "UPDATE", OLD.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME,
26.                                 NEW.PESELID, NEW.VIP_LEVEL, OLD.FIRSTNAME, OLD.LASTNAME,
27.                 OLD.PESELID, OLD.VIP_LEVEL);
28. END $$
29.  
30. CREATE TRIGGER READERS_INSERT AFTER INSERT ON READERS
31. FOR EACH ROW
32. BEGIN
33.         INSERT INTO READERS_AUD(EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME,
34.                                                         NEW_PESELID, NEW_VIP_LEVEL)
35.                 VALUES(CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME,
36.                                 NEW.PESELID, NEW.VIP_LEVEL);
37. END $$
38.  
39. CREATE TRIGGER READERS_DELETE AFTER DELETE ON READERS
40. FOR EACH ROW
41. BEGIN
42.         INSERT INTO READERS_AUD(EVENT_DATE, EVENT_TYPE, READER_ID, OLD_FIRSTNAME, OLD_LASTNAME,
43.                                                         OLD_PESELID,OLD_VIP_LEVEL)
44.                 VALUES(CURTIME(), "DELETE", OLD.READER_ID,  OLD.FIRSTNAME, OLD.LASTNAME,
45.                 OLD.PESELID, OLD.VIP_LEVEL);
46. END $$
47.  
48. DELIMITER ;