MIT8107-SQL-Lab2-TransactionsTriggersAndCheckConstraints-codebreakers/075855-triggers.mysql at main · ADB-course/MIT8107-SQL-Lab2-TransactionsTriggersAndCheckConstraints-codebreakers · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- List all the databases
SHOW DATABASES;
-- switch to the current database
USE 75855_lab2_triggers;
-- list of all tables
SHOW TABLES;

-- STEP 1
SHOW CREATE TABLE employees;
CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL,
  `lastName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `firstName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `extension` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `officeCode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `reportsTo` int DEFAULT NULL,
  `jobTitle` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`employeeNumber`),
  KEY `reportsTo` (`reportsTo`),
  KEY `officeCode` (`officeCode`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
  CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `employees_undo` (
  `date_of_change` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2),
  `employeeNumber` int NOT NULL,
  `lastName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `firstName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `extension` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `officeCode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `reportsTo` int DEFAULT NULL,
  `jobTitle` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `change_type` varchar(50) NOT NULL,
  PRIMARY KEY (`date_of_change`),
  UNIQUE KEY `date_of_change_UNIQUE` (`date_of_change`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- STEP 2
CREATE

 TRIGGER TRG_BEFORE_UPDATE_ON_employees
 BEFORE UPDATE ON employees FOR EACH ROW

 INSERT INTO `employees_undo` SET
 `date_of_change` = CURRENT_TIMESTAMP(2),
 `employeeNumber` = OLD.`employeeNumber` ,
 `lastName` = OLD.`lastName` ,
 `firstName` = OLD.`firstName` ,
 `extension` = OLD.`extension` ,
 `email` = OLD.`email` ,
 `officeCode` = OLD.`officeCode` ,
 `reportsTo` = OLD.`reportsTo` ,
 `jobTitle` = OLD.`jobTitle` ,
 `change_type` = 'An update DML operation was executed';

 -- STEP 3
 SHOW TRIGGERS;