hAJQXJDs

1DELIMITER $$
2
3DROP TABLE IF EXISTS BOOKS_AUD;
4CREATE TABLE BOOKS_AUD (
5  EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
6  EVENT_DATE DATETIME NOT NULL,   
7  EVENT_TYPE VARCHAR(10) DEFAULT NULL,
8  BOOK_ID INT(11),
9  OLD_TITLE VARCHAR(255),
10  NEW_TITLE VARCHAR(255), 
11  OLD_PUBYEAR INT(4),
12  NEW_PUBYEAR INT(4),  
13  OLD_BESTSELLER BINARY, 
14  NEW_BESTSELLER BINARY, 
15  PRIMARY KEY (`EVENT_ID`) 
16  );
17  
18DROP TABLE IF EXISTS READERS_AUD;
19CREATE TABLE READERS_AUD (
20  EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
21  EVENT_DATE DATETIME NOT NULL,   
22  EVENT_TYPE VARCHAR(10) DEFAULT NULL,
23  READER_ID INT(11),
24  OLD_FIRSTNAME VARCHAR(255),
25  NEW_FIRSTNAME VARCHAR(255), 
26  OLD_LASTNAME VARCHAR(255),
27  NEW_LASTNAME VARCHAR(255),
28  OLD_PESELID VARCHAR(11), 
29  NEW_PESELID VARCHAR(11),
30  OLD_VIP_LEVEL VARCHAR(20),
31  NEW_VIP_LEVEL VARCHAR(20), 
32  PRIMARY KEY (`EVENT_ID`) 
33  );
34  
35DROP TRIGGER IF EXISTS kodilla_project.BOOKS_INSERT;    
36CREATE TRIGGER BOOKS_INSERT AFTER INSERT ON BOOKS
37 FOR EACH ROW
38 BEGIN
39	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE,   BOOK_ID,  NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
40		VALUES (CURTIME(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
41END;
42
43 DROP TRIGGER IF EXISTS kodilla_project.BOOKS_DELETE;   
44CREATE TRIGGER BOOKS_DELETE AFTER DELETE ON BOOKS
45 FOR EACH ROW
46 BEGIN
47	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID)
48		VALUES (CURTIME(), "DELETE", OLD.BOOK_ID);
49END;
50
51DROP TRIGGER IF EXISTS kodilla_project.BOOKS_UPDATE;    
52CREATE TRIGGER BOOKS_UPDATE AFTER UPDATE ON BOOKS
53 FOR EACH ROW
54 BEGIN
55	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE, NEW_PUBYEAR,
56							NEW_BESTSELLER, OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER)
57		VALUES (CURTIME(), "DELETE", BOOK_ID, NEW.TITLE, NEW.PUBYEAR,
58							NEW.BESTSELLER, OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER);
59END;
60
61DROP TRIGGER IF EXISTS kodilla_project.READERS_INSERT;  
62CREATE TRIGGER READERS_INSERT AFTER INSERT ON READERS
63 FOR EACH ROW
64 BEGIN
65	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL)
66		VALUES (CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
67END;
68
69DROP TRIGGER IF EXISTS kodilla_project.READERS_DELETE; 
70CREATE TRIGGER READERS_DELETE AFTER DELETE ON READERS
71 FOR EACH ROW
72 BEGIN
73	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID)
74		VALUES (CURTIME(), "DELETE", OLD.READER_ID);
75END;
76
77DROP TRIGGER IF EXISTS kodilla_project.READERS_UPDATE; 
78CREATE TRIGGER READERS_UPDATE AFTER UPDATE ON READERS
79 FOR EACH ROW
80 BEGIN
81	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID,
82							NEW_VIP_LEVEL, OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESELID, OLD_VIP_LEVEL)
83		VALUES (CURTIME(), "DELETE", OLD.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID,
84				NEW.VIP_LEVEL, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL);
85END $$
86
87DELIMITER ;