PZNwJSNW

1CREATE TABLE `kodilla_course`.`books_aud` (
2  `EVENT_ID` INT NOT NULL AUTO_INCREMENT,
3  `EVENT_DATE` DATETIME NOT NULL,
4  `EVENT_TYPE` VARCHAR(10) NULL DEFAULT NULL,
5  `OLD_BOOK_ID` INT NULL DEFAULT NULL,
6  `NEW_BOOK_ID` INT NULL DEFAULT NULL,
7  `OLD_TITLE` VARCHAR(255) NULL DEFAULT NULL,
8  `NEW_TITLE` VARCHAR(255) NULL DEFAULT NULL,
9  `OLD_PUBYEAR` INT NULL DEFAULT NULL,
10  `NEW_PUBYEAR` INT NULL DEFAULT NULL,
11  `OLD_BESTSELLER` TINYINT(1) NULL DEFAULT NULL,
12  `NEW_BESTSELLER` TINYINT(1) NULL DEFAULT NULL,
13  PRIMARY KEY (`EVENT_ID`));
14
15********************************************  
16
17DROP TRIGGER IF EXISTS `kodilla_course`.`books_AFTER_INSERT`;
18
19DELIMITER $$
20USE `kodilla_course`$$
21CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`books_AFTER_INSERT` AFTER INSERT ON `books` FOR EACH ROW
22BEGIN
23	INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, NEW_BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
24	VALUES (CURTIME(), "INSERT", NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
25END$$
26DELIMITER ;
27
28DROP TRIGGER IF EXISTS `kodilla_course`.`books_AFTER_DELETE`;
29
30********************************************
31DROP TRIGGER IF EXISTS `kodilla_course`.`books_AFTER_DELETE`;
32
33DELIMITER $$
34USE `kodilla_course`$$
35CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`books_AFTER_DELETE` AFTER DELETE ON `books` FOR EACH ROW
36BEGIN
37INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, OLD_BOOK_ID, OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER)
38	VALUES (CURTIME(), "DELETE", OLD.BOOK_ID, OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER);
39END$$
40DELIMITER ;
41
42********************************************
43
44DROP TRIGGER IF EXISTS `kodilla_course`.`books_AFTER_UPDATE`;
45
46DELIMITER $$
47USE `kodilla_course`$$
48CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`books_AFTER_UPDATE` AFTER UPDATE ON `books` FOR EACH ROW
49BEGIN
50INSERT INTO BOOKS_AUD (EVENT_DATE, EVENT_TYPE, OLD_BOOK_ID, OLD_TITLE, OLD_PUBYEAR, OLD_BESTSELLER,
51						NEW_BOOK_ID, NEW_TITLE, NEW_PUBYEAR, NEW_BESTSELLER)
52	VALUES (CURTIME(), "UPDATE", OLD.BOOK_ID, OLD.TITLE, OLD.PUBYEAR, OLD.BESTSELLER,
53						NEW.BOOK_ID, NEW.TITLE, NEW.PUBYEAR, NEW.BESTSELLER);
54END$$
55DELIMITER ;
56
57****************************************************************************************
58
59CREATE TABLE `kodilla_course`.`readers_aud` (
60  `EVENT_ID` INT NOT NULL AUTO_INCREMENT,
61  `EVENT_DATE` DATETIME NULL,
62  `EVENT_TYPE` VARCHAR(10) NULL DEFAULT NULL,
63  `OLD_READER_ID` INT NULL DEFAULT NULL,
64  `NEW_READER_ID` INT NULL DEFAULT NULL,
65  `OLD_FIRSTNAME` VARCHAR(255) NULL DEFAULT NULL,
66  `NEW_FIRSTNAME` VARCHAR(255) NULL DEFAULT NULL,
67  `OLD_LASTNAME` VARCHAR(255) NULL DEFAULT NULL,
68  `NEW_LASTNAME` VARCHAR(255) NULL DEFAULT NULL,
69  `OLD_PESEL_ID` VARCHAR(11) NULL DEFAULT NULL,
70  `NEW_PESEL_ID` VARCHAR(11) NULL DEFAULT NULL,
71  `OLD_VIP_LEVEL` VARCHAR(20) NULL DEFAULT NULL,
72  `NEW_VIP_LEVEL` VARCHAR(20) NULL DEFAULT NULL,
73  PRIMARY KEY (`EVENT_ID`));
74  
75******************************************** 
76 
77DROP TRIGGER IF EXISTS `kodilla_course`.`readers_AFTER_INSERT`;
78
79DELIMITER $$
80USE `kodilla_course`$$
81CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`readers_AFTER_INSERT` AFTER INSERT ON `readers` FOR EACH ROW
82BEGIN
83	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, NEW_READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESEL_ID, NEW_VIP_LEVEL)
84	VALUES (CURTIME(), "INSERT", NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
85END$$
86DELIMITER ;
87
88********************************************
89 
90DROP TRIGGER IF EXISTS `kodilla_course`.`readers_AFTER_DELETE`;
91
92DELIMITER $$
93USE `kodilla_course`$$
94CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`readers_AFTER_DELETE` AFTER DELETE ON `readers` FOR EACH ROW
95BEGIN
96	INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, OLD_READER_ID, OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESEL_ID, OLD_VIP_LEVEL)
97	VALUES (CURTIME(), "DELETE", OLD.READER_ID, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL);
98END$$
99DELIMITER ;
100
101********************************************
102
103DROP TRIGGER IF EXISTS `kodilla_course`.`readers_AFTER_UPDATE`;
104
105DELIMITER $$
106USE `kodilla_course`$$
107CREATE DEFINER = CURRENT_USER TRIGGER `kodilla_course`.`readers_AFTER_UPDATE` AFTER UPDATE ON `readers` FOR EACH ROW
108BEGIN
109INSERT INTO READERS_AUD (EVENT_DATE, EVENT_TYPE, OLD_READER_ID, OLD_FIRSTNAME, OLD_LASTNAME, OLD_PESEL_ID, OLD_VIP_LEVEL,
110				NEW_READER_ID, NEW_FIRSTNAME, NEW_LASTNAME, NEW_PESEL_ID, NEW_VIP_LEVEL)
111	VALUES (CURTIME(), "UPDATE", OLD.READER_ID, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PESELID, OLD.VIP_LEVEL,
112				NEW.READER_ID, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PESELID, NEW.VIP_LEVEL);
113END$$
114DELIMITER ;
115