2w2udgUu

1DELIMITER ;
2
3DROP TABLE IF EXISTS BOOKS_AUD;
4
5CREATE TABLE BOOKS_AUD (
6	EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
7    EVENT_DATE DATETIME NOT NULL,
8    EVENT_TYPE VARCHAR(10) DEFAULT NULL,
9    BOOK_ID INT(11) NOT NULL,
10    OLD_TITLE VARCHAR(255),
11    NEW_TITLE VARCHAR(255),
12    OLD_PUBYEAR INT(4),
13    NEW_PUBYEAR INT(4),
14    OLD_BESTSELLER BOOLEAN,
15    NEW_BESTSELLER BOOLEAN,
16    PRIMARY KEY(EVENT_ID)
17    );
18
19
20DROP TABLE IF EXISTS READERS_AUD;
21    
22CREATE TABLE READERS_AUD (
23	EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
24    EVENT_DATE DATETIME NOT NULL,
25    EVENT_TYPE VARCHAR(10) DEFAULT NULL,
26    READER_ID INT(11) NOT NULL,
27    OLD_FIRSTNAME VARCHAR(255),
28    NEW_FIRSTNAME VARCHAR(255),
29    OLD_LASTNAME VARCHAR(255),
30    NEW_LASTNAME VARCHAR(255),
31    OLD_PESEL VARCHAR(11),
32    NEW_PESEL VARCHAR(11),
33    OLD_VIPLEVEL VARCHAR(11),
34    NEW_VIPLEVEL VARCHAR(11),
35    PRIMARY KEY(EVENT_ID)
36    );
37
38DELIMITER $$ 
39
40DROP TRIGGER IF EXISTS BOOKS_INSERT;
41
42CREATE TRIGGER BOOKS_INSERT AFTER INSERT ON BOOKS
43FOR EACH ROW
44BEGIN
45	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
46		VALUES (CURTIME(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
47END $$
48
49DELIMITER;
50
51DELIMITER $$
52
53
54DROP TRIGGER IF EXISTS READERS_INSERT;
55
56CREATE TRIGGER READERS_INSERT AFTER INSERT ON READERS
57FOR EACH ROW
58BEGIN
59	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESEL, NEW_VIPLEVEL)
60		VALUES(CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIPLEVEL);
61END $$
62
63DELIMITER ;
64
65
66DELIMITER $$
67
68DROP TRIGGER IF EXISTS BOOKS_DELETE;
69
70CREATE TRIGGER BOOKS_DELETE AFTER DELETE ON BOOKS
71FOR EACH ROW
72BEGIN
73	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID)
74		VALUES(CURTIME(), "DELETE", OLD.RENT_ID);
75END $$
76
77DELIMITER ;
78
79DELIMITER $$
80
81
82DROP TRIGGER IF EXISTS READERS_DELETE;
83CREATE TRIGGER READERS_DELETE AFTER DELETE ON READERS
84FOR EACH ROW
85BEGIN 
86	INSERT INTO READERS_AUD(EVENT_DATE, EVENT_TYPE, READER_ID)
87		VALUES(CURTIME(), "DELETE", OLD.READER_ID);
88END $$
89
90DELIMITER ;
91
92DROP TRIGGER IF EXISTS BOOKS_UPDATE;
93DELIMITER $$ 
94CREATE TRIGGER BOOKS_UPDATE AFTER UPDATE ON BOOKS
95FOR EACH ROW
96BEGIN
97	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, OLD_TITLE, NEW_TITLE, OLD_PUBYEAR, NEW_PUBYEAR, OLD_BESTSELLER, NEW_BESTSELLER)
98		VALUES(CURTIME(), "UPDATE", OLD.BOOK_ID, OLD.TITLE, NEW.TITLE, OLD.PUBYEAR, NEW.PUBYEAR, OLD.BESTSELLER, NEW.BESTSELLER);
99END $$
100
101DELIMITER ;
102
103DELIMITER $$
104DROP TRIGGER IF EXISTS READERS_UPDATE;
105CREATE TRIGGER READERS_UPDATE AFTER UPDATE ON READERS
106FOR EACH ROW
107BEGIN
108	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID,OLD_FIRSTNAME,NEW_FIRSTNAME,OLD_LASTNAME,NEW_LASTNAME,OLD_PESEL,NEW_PESEL,OLD_VIPLEVEL,NEW_VIPLEVEL)
109		VALUES(CURTIME(), "UPDATE", OLD.READER_ID, OLD.FIRSTNAME, NEW.FIRSTNAME, OLD.LASTNAME, NEW.LASTNAME, OLD.PESELID, NEW.PESELID, OLD.VIPLEVEL, NEW.VIPLEVEL);
110END $$
111
112DELIMITER ;