· 4 years ago · Feb 28, 2021, 08:50 PM
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 BOOL,
11 NEW_BESTSELLER BOOL,
12 PRIMARY KEY (`EVENT_ID`)
13);
14
15
16
17
18DROP TRIGGER IF EXISTS `kodilla_course`.`BOOKS_INSERT`;
19
20DELIMITER $$
21
22CREATE TRIGGER BOOKS_INSERT AFTER INSERT ON BOOKS
23FOR EACH ROW
24BEGIN
25 INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE,
26 NEW_PUBYEAR, NEW_BESTSELLER)
27 VALUES (CURTIME(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
28END $$
29
30DELIMITER ;
31
32
33
34
35DROP TRIGGER IF EXISTS `kodilla_course`.`BOOKS_DELETE`;
36
37DELIMITER $$
38
39CREATE TRIGGER BOOKS_DELETE AFTER DELETE ON BOOKS
40FOR EACH ROW
41BEGIN
42 INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID)
43 VALUES (CURTIME(), "DELETE", OLD.BOOK_ID);
44END $$
45
46DELIMITER ;
47
48
49
50
51DROP TRIGGER IF EXISTS `kodilla_course`.`BOOKS_UPDATE`;
52
53DELIMITER $$
54
55CREATE TRIGGER BOOKS_UPDATE AFTER UPDATE ON BOOKS
56FOR EACH ROW
57BEGIN
58 INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, BOOK_ID, NEW_TITLE,
59 NEW_PUBYEAR, NEW_BESTSELLER, OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER)
60 VALUES (CURTIME(), "UPDATE", OLD.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER,
61 OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER);
62END $$
63
64DELIMITER ;
65
66
67
68
69Create table READERS_AUD (
70 EVENT_ID INT(11) NOT NULL AUTO_INCREMENT,
71 EVENT_DATE DATETIME NOT NULL,
72 EVENT_TYPE VARCHAR(10) DEFAULT NULL,
73 READER_ID INT(11) NOT NULL,
74 OLD_FIRSTNAME VARCHAR(255),
75 NEW_FIRSTNAME VARCHAR(255),
76 OLD_LASTNAME VARCHAR(255),
77 NEW_LASTNAME VARCHAR(255),
78 OLD_PESELID VARCHAR(11),
79 NEW_PESELID VARCHAR(11),
80 OLD_VIPLEVEL VARCHAR(255),
81 NEW_VIPLEVEL VARCHAR(255),
82 PRIMARY KEY (`EVENT_ID`)
83);
84
85
86
87
88DROP TRIGGER IF EXISTS `kodilla_course`.`READERS_INSERT`;
89
90DELIMITER $$
91
92CREATE TRIGGER READERS_INSERT AFTER INSERT ON READERS
93FOR EACH ROW
94BEGIN
95 INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID, NEW_FIRSTNAME,
96 NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL)
97 VALUES (CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME,
98 NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
99END $$
100
101DELIMITER ;
102
103
104
105
106DROP TRIGGER IF EXISTS `kodilla_course`.`READERS_DELETE`;
107
108DELIMITER $$
109
110CREATE TRIGGER READERS_DELETE AFTER DELETE ON READERS
111FOR EACH ROW
112BEGIN
113 INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID)
114 VALUES (CURTIME(), "DELETE", OLD.READER_ID);
115END $$
116
117DELIMITER ;
118
119
120
121
122DROP TRIGGER IF EXISTS `kodilla_course`.`READERS_UPDATE`;
123
124DELIMITER $$
125
126CREATE TRIGGER READERS_UPDATE AFTER UPDATE ON READERS
127FOR EACH ROW
128BEGIN
129 INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, READER_ID,
130 NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESELID, NEW_VIP_LEVEL,
131 OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESELID, OLD_VIP_LEVEL)
132 VALUES (CURTIME(), "UPDATE", OLD.READER_ID,
133 NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL,
134 OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL);
135END $$
136
137DELIMITER ;