· 6 years ago · Mar 31, 2019, 11:44 AM
1CREATE 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 TINYINT(1),
11 new_bestseller TINYINT(1),
12 PRIMARY KEY(`event_id`)
13);
14
15/* ----- */
16
17DROP TRIGGER IF EXISTS books_insert;
18
19DELIMITER $$
20
21CREATE TRIGGER books_insert AFTER INSERT ON books
22FOR EACH ROW
23BEGIN
24 INSERT INTO books_aud(event_date, event_type, book_id, new_title, new_pubyear, new_bestseller)
25 VALUES(CURTIME(), "INSERT", new.book_id, new.title, new.pubyear, new.bestseller);
26END $$
27
28DELIMITER ;
29
30/* ----- */
31
32DROP TRIGGER IF EXISTS books_delete;
33
34DELIMITER $$
35
36CREATE TRIGGER books_delete AFTER DELETE ON books
37FOR EACH ROW
38BEGIN
39 INSERT INTO books_aud(event_date, event_type, book_id)
40 VALUES(CURTIME(), "DELETE", old.book_id);
41END $$
42
43DELIMITER ;
44
45/* ----- */
46
47DROP TRIGGER IF EXISTS books_update;
48
49DELIMITER $$
50
51CREATE TRIGGER books_update AFTER UPDATE ON books
52FOR EACH ROW
53BEGIN
54 INSERT INTO books_aud(event_date, event_type, book_id, new_title, new_pubyear, new_bestseller,
55 old_title, old_pubyear, old_bestseller)
56 VALUES(CURTIME(), "UPDATE", new.book_id, new.title, new.pubyear, new.bestseller,
57 old.title, old.pubyear, old.bestseller);
58END $$
59
60DELIMITER ;
61
62/* ----- */
63/* ----- */
64
65INSERT INTO books(book_id, title, pubyear)
66 VALUES(7, "MySQL for smarties", "2013");
67
68INSERT INTO books(title, pubyear)
69 VALUES("Java for dummies", "2019");
70
71COMMIT;
72
73/* ----- */
74
75DELETE FROM books WHERE book_id = 9;
76
77/* ----- */
78
79UPDATE books
80SET
81 title = "The Flute with Six Holes",
82 pubyear = 1958
83WHERE book_id = 10;
84
85/* ----- */
86
87CALL UpdateBestsellers();
88
89/* ----- */
90
91SELECT * FROM kodilla_course.books_aud;
92
93/* ----- */
94/* ----- */
95/* ----- */
96
97CREATE TABLE readers_aud (
98 event_id INT(11) NOT NULL AUTO_INCREMENT,
99 event_date DATETIME NOT NULL,
100 event_type VARCHAR(10) DEFAULT NULL,
101 reader_id INT(11) NOT NULL,
102 old_firstname VARCHAR(255),
103 new_firstname VARCHAR(255),
104 old_lastname VARCHAR(255),
105 new_lastname VARCHAR(255),
106 old_peselid VARCHAR(11),
107 new_peselid VARCHAR(11),
108 old_vip_level VARCHAR(20),
109 new_vip_level VARCHAR(20),
110 PRIMARY KEY(`event_id`)
111);
112
113/* ----- */
114
115DROP TRIGGER IF EXISTS readers_insert;
116
117DELIMITER $$
118
119CREATE TRIGGER readers_insert AFTER INSERT ON readers
120FOR EACH ROW
121BEGIN
122 INSERT INTO readers_aud(event_date, event_type, reader_id,
123 new_firstname, new_lastname, new_peselid, new_vip_level)
124 VALUES(CURTIME(), "INSERT", new.reader_id,
125 new.firstname, new.lastname, new.peselid, new.vip_level);
126END $$
127
128DELIMITER ;
129
130/* ----- */
131
132DROP TRIGGER IF EXISTS readers_delete;
133
134DELIMITER $$
135
136CREATE TRIGGER readers_delete AFTER DELETE ON readers
137FOR EACH ROW
138BEGIN
139 INSERT INTO readers_aud(event_date, event_type, reader_id)
140 VALUES(CURTIME(), "DELETE", old.reader_id);
141END $$
142
143DELIMITER ;
144
145/* ----- */
146
147DROP TRIGGER IF EXISTS readers_update;
148
149DELIMITER $$
150
151CREATE TRIGGER readers_update AFTER UPDATE ON readers
152FOR EACH ROW
153BEGIN
154 INSERT INTO readers_aud(event_date, event_type, reader_id,
155 new_firstname, new_lastname, new_peselid, new_vip_level,
156 old_firstname, old_lastname, old_peselid, old_vip_level)
157 VALUES(CURTIME(), "UPDATE", new.reader_id,
158 new.firstname, new.lastname, new.peselid, new.vip_level,
159 old.firstname, old.lastname, old.peselid, old.vip_level);
160END $$
161
162DELIMITER ;
163
164/* ----- */
165/* ----- */
166
167INSERT INTO readers(firstname, lastname, peselid, vip_level)
168 VALUES("John", "Doe", "73010612345", null);
169
170INSERT INTO readers(firstname, lastname, peselid, vip_level)
171 VALUES("Jane", "Doe", "78102367890", null);
172
173COMMIT;
174
175/* ----- */
176
177DELETE FROM readers WHERE reader_id = 9;
178
179/* ----- */
180
181UPDATE readers
182SET
183 firstname = "Jonathan",
184 lastname = "Donovan",
185 peselid = "72010612347"
186WHERE reader_id = 8;
187
188/* ----- */
189
190CALL UpdateVipLevels();
191
192/* ----- */
193
194SELECT * FROM kodilla_course.readers_aud;