1. CREATE TABLE BOOKS_AUD (
2.         EVENT_ID INT(11) NOT NULL auto_increment,
3.     EVENT_DATE datetime not null,
4.     EVENT_TYPE VARCHAR(10) default null,
5.     BOOK_ID INT(11) NOT NULL,
6.     OLD_TITLE VARCHAR(255),
7.     NEW_TITLE VARCHAR(255),
8.     OLD_PUBYEAR INT(4),
9.     NEW_PUBYEAR INT(4),
10.     OLD_BESTSELLER BOOLEAN,
11.     NEW_BESTSELLER BOOLEAN,
12.     PRIMARY KEY (`EVENT_ID`)
13. );
14.  
15. CREATE TABLE READERS_AUD (
16.         EVENT_ID INT(11) NOT NULL auto_increment,
17.     EVENT_DATE datetime not null,
18.     EVENT_TYPE VARCHAR(10) default null,
19.     READER_ID INT(11) NOT NULL,
20.     OLD_FIRSTNAME VARCHAR(255),
21.     NEW_FIRSTNAME VARCHAR(255),
22.     OLD_LASTNAME VARCHAR(255),
23.     NEW_LASTNAME VARCHAR(255),
24.     OLD_PESELID VARCHAR(11),
25.     NEW_PESELID VARCHAR(11),
26.     OLD_VIP_LEVEL VARCHAR(20),
27.     NEW_VIP_LEVEL VARCHAR(20),
28.     PRIMARY KEY (`EVENT_ID`)
29. );
30.  
31. DELIMITER $$
32.  
33. create trigger BOOKS_INSERT AFTER insert on BOOKS
34. for each row
35. begin
36.         insert into BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
37.                 values (curtime(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
38. end$$
39.  
40. create trigger BOOKS_DELETE AFTER delete on BOOKS
41. for each row
42. begin
43.         insert into BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID)
44.                 values (curtime(), "DELETE", OLD.BOOK_ID);
45. end$$
46.  
47. create trigger BOOKS_UPDATE AFTER update on BOOKS
48. for each row
49. begin
50.         insert into BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER,
51.                                                         OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER)
52.                 values (curtime(), "UPDATE", OLD.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER,
53.                                                         OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER);
54. end$$
55.  
56. create trigger READERS_INSERT AFTER insert on readers
57. for each row
58. begin
59.         insert into READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL)
60.                 values (curtime(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
61. end$$
62.  
63. create trigger READERS_DELETE AFTER delete on readers
64. for each row
65. begin
66.         insert into READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID)
67.                 values (curtime(), "DELETE", OLD.READER_ID);
68. end$$
69.  
70. create trigger READERS_UPDATE AFTER update on readers
71. for each row
72. begin
73.         insert into READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL,
74.                                                         OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESELID, OLD_VIP_LEVEL)
75.                 values (curtime(), "UPDATE", OLD.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL,
76.                                                         OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL);
77. end$$
78.  
79. DELIMITER ;