· 6 years ago · Jun 20, 2019, 01:48 PM
1:blush: :smiley: :relaxed: :+1: :wave: :rose: :herb: :palm_tree: :leaves: :blossom: :cactus: :four_leaf_clover: :hibiscus: :evergreen_tree: :seedling: :bell: :gift: :turtle: :snowman: :zap: :notes: :sparkles: :heart: :foggy: :blue_heart: :v: :christmas_tree: :santa: :pushpin: :basketball: :golf: :books: :guitar: :tangerine: :corn: :eggplant: :tomato: :strawberry: :green_apple: :cherries: :lemon:
2
3#### :pushpin: Các bảng trong CSDL
4
5> Để có thể thực hành với triggers
6> trong sql ta tạo các bảng trong cơ sở dữ liệu
7> với cấu trúc các cột và kiểu dữ liệu dưới đây.
8
9:one: Bảng **Student**
10
11```sql
12CREATE TABLE `student` (
13 `student_id` varchar(45) NOT NULL,
14 `name` varchar(45) NOT NULL,
15 `date_of_birth` date NOT NULL,
16 `student_class` varchar(45) NOT NULL,
17 `edu_program` varchar(45) NOT NULL,
18 `training_system` varchar(45) NOT NULL,
19 `state` varchar(45) NOT NULL,
20 `year` varchar(45) NOT NULL,
21 `department` varchar(45) NOT NULL,
22 `email` varchar(45) NOT NULL,
23 `tuition_unit` float UNSIGNED NOT NULL,
24 PRIMARY KEY(`student_id`)
25);
26```
27
28:two: Bảng **Subject**
29
30```sql
31CREATE TABLE `subject` (
32 `subject_id` varchar(45) NOT NULL,
33 `subject_name` varchar(255) NOT NULL,
34 `duration` varchar(45),
35 `credit` int(11) NOT NULL,
36 `tuition_credit` float NOT NULL,
37 `weight` float NOT NULL,
38 PRIMARY KEY(`subject_id`)
39);
40```
41
42:three: Bảng **Class**
43
44```sql
45CREATE TABLE `class` (
46 `id` int PRIMARY KEY AUTOINCREMENT,
47 `class_id` varchar(255) NOT NULL,
48 `class_subject_id` varchar(255) NOT NULL,
49 `start_time` time NOT NULL,
50 `end_time` time NOT NULL,
51 `day` varchar(45) NOT NULL,
52 `week` varchar(45) NOT NULL,
53 `class_type` varchar(45) NOT NULL,
54 `group_class` varchar(45) NOT NULL,
55 `room` varchar(45) NOT NULL,
56 `semester` varchar(45) NOT NULL,
57 `total_registered` int NOT NULL,
58 `max_register` int NOT NULL,
59 `expiration_date` date,
60 FOREIGN KEY(`class_subject_id`) REFERENCES `subject`(`subject_id`)
61);
62```
63
64:four: Bảng **Register**
65
66```sql
67CREATE TABLE `register` (
68 `id` int PRIMARY KEY AUTOINCREMENT,
69 `student_id` varchar(45) NOT NULL,
70 `register_class_id` varchar(255) NOT NULL,
71 FOREIGN KEY(`student_id`) REFERENCES `student`(`student_id`),
72 FOREIGN KEY(`register_class_id`) REFERENCES `class`(`class_id`)
73);
74```
75
76#### :pushpin: Triggers examples
77
78:+1: Ex01: **BEFORE_INSERT** - Nếu thời gian đăng kí lớp đã hết sinh viên sẽ không đăng kí được lớp nữa, hoặc trong thời gian đăng kí mà số lượng sinh viên iuar lớp đó đã đầy, hoặc sinh viên đăng kí mã lớp không tông tại hoặc đã đăng kí rồi.
79
80```sql
81BEGIN
82 IF((SELECT CURDATE()) > (SELECT date_to_exist FROM class WHERE NEW.register_class_id = class_id))
83 THEN
84 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Đã hết thời gian đăng kí lớp!';
85 END IF;
86
87 IF((SELECT total_registered FROM class WHERE NEW.register_class_id = class_id) >= (SELECT max_register FROM class WHERE NEW.register_class_id = class_id))
88 THEN
89 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Đã hết chỗ đăng kí lớp!';
90 // RAISE (ABORT, 'message');
91 END IF;
92
93 IF EXISTS (SELECT student_id, register_class_id FROM register WHERE student_id = NEW.student_id AND register_class_id = NEW.register_class_id) THEN
94 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Bạn đã đăng kí mã lớp này rồi!';
95 END IF;
96
97 IF (NEW.register_class_id NOT IN (SELECT class_id FROM class)) THEN //using WHEN END, RAISE message with SQLite
98 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Không tồn tại mã lớp đăng kí.';
99 END IF;
100
101END
102
103```
104
105:+1: Ex02: **AFTER_DELETE** - Sau khi một sinh viên xóa lớp học đã đăng kí thì số lượng sinh viên đăng kí lớp học đó sẽ giảm đi 1 (trong bảng class).
106
107```sql
108BEGIN
109 UPDATE class SET max_register = total_registered - 1
110 WHERE class.class_id = OLD.register_class_id;
111END
112
113```
114
115:+1: Ex03: **BEFORE_DELETE_REGISTER** - Nếu hết thời gian đăng kí lớp học thì sinh viên sẽ không được quyền xóa đăng kí lớp học nữa.
116
117```sql
118BEGIN
119
120 IF((SELECT CURDATE()) > (SELECT expiration_date FROM class WHERE OLD.register_class_id = class_id)) THEN
121 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Đã hết thời gian điều chỉnh đăng kí lớp!';
122 END IF;
123
124END
125```
126
127:+1: Ex04: **AFTER_INSERT_REGISTER** - Kiểm tra nếu sinh viên đăng kí quá 24 tín chỉ thì sẽ không được đăng kí thêm lớp học nữa, hoặc nếu chưa vượt quá số tín chỉ quy định nhưng lớp vừa đăng kí đã trùng thời gianơi học với lớp đã đăng kí rồi.
128
129```sql
130BEGIN
131 DECLARE id_student_param VARCHAR(45);
132 DECLARE class_id_param VARCHAR(45);
133 SET id_student_param = NEW.student_id;
134 SET class_id_param = NEW.register_class_id;
135
136 UPDATE class SET total_registered = total_registered + 1
137 WHERE class.class_id = class_id_param;
138
139IF((SELECT SUM(credit)
140 FROM subject, class, register
141 WHERE subject.subject_id = class.class_subject_id
142 AND register.register_class_id = class.class_id GROUP BY student_id) > 24)
143 THEN
144 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Bạn đã đăng kí quá số tín chỉ quy định!';
145END IF;
146
147IF((SELECT COUNT(overlap1.register_class_id)
148FROM register overlap1, register overlap2
149WHERE (overlap2.start_time BETWEEN overlap1.start_time AND overlap1.end_time
150 OR overlap2.end_time BETWEEN overlap1.start_time AND overlap1.end_time)
151 AND overlap1.register_class_id <> overlap2.register_class_id
152 AND overlap1.day = overlap2.day
153 AND overlap1.student_id = overlap2.student_id) > 0) THEN
154SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mã lớp đăng kí đã bị trùng thời gian học.';
155END IF;
156
157END;
158```