PDP6ZWwK

12. DROP TABLE IF EXISTS HISTORICALAUDIT; 
2
3CREATE TABLE HistoricalAudit(TableName varChar(20), ColumnName varChar(20), action varChar(10), OldValue varChar(20), NewValue varChar(20), PublisherCode char(3), AuthorNum decimal(2, 0), BookCode char(4), UserId varChar(20), LogTimeStamp DATETIME);
4
5DROP TRIGGER IF EXISTS insertAuthorNum;
6DROP TRIGGER IF EXISTS updateAuthorNum;
7DROP TRIGGER IF EXISTS deleteAuthorNum;
8DROP TRIGGER IF EXISTS insertBook;
9DROP TRIGGER IF EXISTS updateBook;
10DROP TRIGGER IF EXISTS deleteBook;
11DROP TRIGGER IF EXISTS insertCopy;
12DROP TRIGGER IF EXISTS updateCopy;
13DROP TRIGGER IF EXISTS deleteCopy;
14DROP TRIGGER IF EXISTS insertInventory;
15DROP TRIGGER IF EXISTS updateInventory;
16DROP TRIGGER IF EXISTS deleteInventory;
17DROP TRIGGER IF EXISTS inserPublisher;
18DROP TRIGGER IF EXISTS updatePublisher;
19DROP TRIGGER IF EXISTS deletePublisher;
20DROP TRIGGER IF EXISTS insertWrote;
21DROP TRIGGER IF EXISTS updateWrote;
22DROP TRIGGER IF EXISTS deleteWrote;
23
24delimiter //
25CREATE TRIGGER insertAuthorNum
26AFTER INSERT ON AUTHOR
27FOR EACH ROW
28BEGIN
29IF NEW.authorNum IS NOT NULL
30THEN
31INSERT INTO HISTORICALAUDIT
32VALUES
33('AUTHOR', 'authorNum',  ‘INSERT’, NULL, NEW.authorNum, NULL, NEW.authorNum, NULL, USER(), current_timestamp());
34END IF;
35END
36delimiter ;
37
38delimiter //
39CREATE TRIGGER updateAuthorNum
40AFTER UPDATE ON AUTHOR
41FOR EACH ROW
42BEGIN
43IF NEW.authorNum IS NOT NULL 
44THEN
45INSERT INTO HISTORICALAUDIT
46VALUES
47('AUTHOR', 'authorNum', ‘UPDATE’, OLD.authorNum, NEW.authorNum, NULL, NEW.authorNum, NULL, USER(), current_timestamp());
48END IF;
49END;
50
51delimiter ;
52
53delimiter //
54CREATE TRIGGER deleteAuthorNum
55BEFORE DELETE ON AUTHOR
56FOR EACH ROW
57BEGIN
58IF OLD.authorNum IS NOT NULL 
59THEN
60INSERT INTO HISTORICALAUDIT
61VALUES
62('AUTHOR', 'authorNum', ‘DELETE’, OLD.authorNum, NULL, NULL, NULL, NULL, USER(), current_timestamp());
63END IF;
64END;
65
66delimiter ;
67
68delimiter //
69CREATE TRIGGER insertBook 
70AFTER INSERT ON BOOK
71FOR EACH ROW
72BEGIN
73IF NEW.bookCode IS NOT NULL OR NEW.publisherCode IS NOT NULL
74THEN
75INSERT INTO HISTORICALAUDIT
76VALUES
77('BOOK', 'bookcode', ‘INSERT’, NULL, NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
78
79INSERT INTO HISTORICALAUDIT
80VALUES
81('BOOK', 'publishercode', ‘INSERT’, NULL, NEW.publisherCode, NEW.publisherCode, NULL, NULL, USER(), current_timestamp());
82END IF;
83
84END;
85
86delimiter ;
87
88delimiter //
89CREATE TRIGGER updateBook 
90BEFORE UPDATE ON BOOK
91FOR EACH ROW
92BEGIN
93IF NEW.bookCode IS NOT NULL OR NEW.publisherCode IS NOT NULL
94THEN
95INSERT INTO HISTORICALAUDIT
96VALUES
97('BOOK', 'bookcode', ‘UPDATE’, bookCode, NEW.bookCode, publisherCode, NULL, NEW.bookCode, USER(), current_timestamp());
98
99INSERT INTO HISTORICALAUDIT
100VALUES
101('BOOK', 'pubishercode', ‘UPDATE’, publisherCode, NEW.publisherCode, NEW.publisherCode, NULL, bookCode, USER(), current_timestamp());
102END IF;
103
104END;
105
106delimiter ;
107
108delimiter //
109CREATE TRIGGER deleteBook 
110BEFORE DELETE ON BOOK
111FOR EACH ROW
112BEGIN
113IF bookCode IS NOT NULL OR publisherCode IS NOT NULL
114THEN
115INSERT INTO HISTORICALAUDIT
116VALUES
117('BOOK', 'bookcode', ‘DELETE’, bookCode,NULL, publisherCode, NULL, NULL, USER(), current_timestamp());
118
119INSERT INTO HISTORICALAUDIT
120VALUES
121('BOOK', 'pubishercode', ‘DELETE’, publisherCode, NULL, NULL, NULL, bookCode, USER(), current_timestamp());
122END IF;
123
124END;
125
126delimiter ;
127
128
129delimiter //
130CREATE TRIGGER insertCopy 
131AFTER INSERT ON COPY
132FOR EACH ROW
133BEGIN
134IF NEW.bookCode IS NOT NULL
135THEN
136INSERT INTO HISTORICALAUDIT
137VALUES
138('COPY', 'bookcode', ‘INSERT’, NULL,NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
139
140
141END IF;
142
143END;
144
145delimiter ;
146
147
148delimiter //
149CREATE TRIGGER updateCopy 
150BEFORE UPDATE ON COPY
151FOR EACH ROW
152BEGIN
153IF NEW.bookCode IS NOT NULL
154THEN
155INSERT INTO HISTORICALAUDIT
156VALUES
157('COPY', 'bookcode', ‘UPDATE’, bookCode,NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
158
159
160END IF;
161
162END;
163
164delimiter ;
165
166
167delimiter //
168CREATE TRIGGER deleteCopy 
169BEFORE DELETE ON COPY
170FOR EACH ROW
171BEGIN
172IF bookCode IS NOT NULL
173THEN
174INSERT INTO HISTORICALAUDIT
175VALUES
176('COPY', 'bookcode', ‘DELETE’, bookCode,NULL, NULL, NULL, NULL, USER(), current_timestamp());
177
178
179END IF;
180
181END;
182
183delimiter ;
184
185delimiter //
186CREATE TRIGGER insertInventory 
187AFTER INSERT ON INVENTORY
188FOR EACH ROW
189BEGIN
190IF NEW.bookCode IS NOT NULL
191THEN
192INSERT INTO HISTORICALAUDIT
193VALUES
194('INVENTORY', 'bookcode', ‘INSERT’,  NULL, NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
195
196
197END IF;
198
199END;
200
201delimiter ;
202
203delimiter //
204CREATE TRIGGER updateInventory 
205BEFORE UPDATE ON INVENTORY
206FOR EACH ROW
207BEGIN
208IF NEW.bookCode IS NOT NULL
209THEN
210INSERT INTO HISTORICALAUDIT
211VALUES
212('INVENTORY’, 'bookcode', ‘UPDATE’, bookCode, NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
213
214
215END IF;
216
217END;
218
219delimiter ;
220
221
222delimiter //
223CREATE TRIGGER deleteInventory 
224BEFORE DELETE ON INVENTORY
225FOR EACH ROW
226BEGIN
227IF bookCode IS NOT NULL
228THEN
229INSERT INTO HISTORICALAUDIT
230VALUES
231('INVENTORY', 'bookcode', ‘DELETE’, bookCode, NULL, NULL, NULL, NULL, USER(), current_timestamp());
232
233
234END IF;
235
236END;
237
238delimiter ;
239
240
241delimiter //
242CREATE TRIGGER insertPublisher
243AFTER INSERT ON PUBLISHER
244FOR EACH ROW
245BEGIN
246IF NEW.publisherCode IS NOT NULL
247THEN
248INSERT INTO HISTORICALAUDIT
249VALUES
250('PUBLISHER', 'publisherCode', ‘INSERT’, NULL, NEW.publisherCode, NEW.publisherCode, NULL, NULL, USER(), current_timestamp());
251
252
253END IF;
254
255END;
256delimiter ;
257
258
259delimiter //
260CREATE TRIGGER updatePublisher
261BEFORE UPDATE ON PUBLISHER
262FOR EACH ROW
263BEGIN
264IF NEW.publisherCode IS NOT NULL
265THEN
266INSERT INTO HISTORICALAUDIT
267VALUES
268('PUBLISHER', 'publisherCode', ‘UPDATE’, publisherCode, NEW.publisherCode, NEW.publisherCode, NULL, NULL, USER(), current_timestamp());
269
270
271END IF;
272
273END;
274delimiter ;
275
276
277delimiter //
278CREATE TRIGGER deletePublisher
279BEFORE DELETE ON PUBLISHER
280FOR EACH ROW
281BEGIN
282IF publisherCode IS NOT NULL
283THEN
284INSERT INTO HISTORICALAUDIT
285VALUES
286('PUBLISHER', 'publisherCode', ‘DELETE’, publisherCode, NULL, NULL, NULL, NULL, USER(), current_timestamp());
287
288
289END IF;
290
291END;
292delimiter ;
293
294
295delimiter //
296CREATE TRIGGER insertWrote
297AFTER INSERT ON WROTE
298FOR EACH ROW
299BEGIN
300IF NEW.bookCode IS NOT NULL OR NEW.authorNum IS NOT NULL
301THEN
302INSERT INTO HISTORICALAUDIT
303VALUES
304('WROTE', 'bookCode', ‘INSERT’, NULL, NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
305
306
307END IF;
308IF NEW.authorNum IS NOT NULL
309THEN INSERT INTO HISTORICALAUDIT
310VALUES
311('WROTE', 'authorNum', ‘INSERT’, NULL, NEW.authorNum, NULL, NEW.authorNum, NEW.bookCode, USER(), current_timestamp());
312END IF;
313END;
314delimiter ;
315
316
317delimiter //
318CREATE TRIGGER updateWrote
319BEFORE UPDATE ON WROTE
320FOR EACH ROW
321BEGIN
322IF NEW.bookCode IS NOT NULL
323THEN
324INSERT INTO HISTORICALAUDIT
325VALUES
326('WROTE', 'bookCode', ‘UPDATE’, bookCode, NEW.bookCode, NULL, NULL, NEW.bookCode, USER(), current_timestamp());
327
328
329END IF;
330IF NEW.authorNum IS NOT NULL
331THEN INSERT INTO HISTORICALAUDIT
332VALUES
333('WROTE', 'authorNum', ‘UPDATE’, authorNum, NEW.authorNum, NULL, NEW.authorNum, NEW.bookCode, USER(), current_timestamp());
334END IF;
335END;
336delimiter ;
337
338
339delimiter //
340CREATE TRIGGER deleteWrote
341BEFORE DELETE ON WROTE
342FOR EACH ROW
343BEGIN
344IF bookCode IS NOT NULL
345THEN
346INSERT INTO HISTORICALAUDIT
347VALUES
348('WROTE', 'bookCode', ‘DELETE’, bookCode, NULL, NULL, NULL, NULL, USER(), current_timestamp());
349
350
351END IF;
352IF authorNum IS NOT NULL
353THEN INSERT INTO HISTORICALAUDIT
354VALUES
355('WROTE', 'authorNum', ‘DELETE’, authorNum, NULL, NULL, NULL, NULL, USER(), current_timestamp());
356END IF;
357END;
358delimiter ;
359
360
361INSERT INTO AUTHOR
362VALUES
363('99', 'reas', ' ex');
364
365INSERT INTO BOOK
366VALUES
367('sdd', 'EXAMPLE', 'od', 'os', 'y');
368
369INSERT INTO COPY
370VALUES
371('osl', '2', '3', 'osld', '30');
372
373INSERT INTO INVENTORY
374VALUES
375('udk', '9', '2');
376
377INSERT INTO PUBLISHER
378VALUES
379('od', 'alsf', 'oasd');
380
381INSERT INTO WROTE
382VALUES
383('9', '3', '3');
384SELECT *
385FROM HISTORICALAUDIT