MySQL Trigger(1)
📢trigger(트리거) : 연쇄반응테이블에 대한 이벤트(insert, update, delete)에 반응하여 자동으로 실행되는 작업.이벤트가 발생했을 때 데이터의 무결성(일관성)을 지키기 위해 사용.--트리거 구문drop tr
koop.tistory.com
지난 포스팅에 이어서 실습
--school 데이터베이스에서 처리
--1. course 테이블에 해당 코스의 수강인원을 집계하는 필드를 생성
--필드명 : co_degree
--co_degree 필드에 해당 코스를 듣고 있는 학생을 집계하여 업데이트
alter table course add co_degree int;
update course set
co_degree=(
select count(at_co_code) from attend
where at_co_code = co_code
group by at_co_code
);
mysql> select * from course;
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| co_code | co_name | co_professor | co_point | co_time | co_timetable | co_degree |
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| 2020ipc001 | 컴퓨터개론 | 유관순 | 2 | 2 | 화1A,1B,2A,2B | 2 |
| 2020ipc002 | 기초전기 | 이순신 | 3 | 4 | 월1A,1B,2A목1A,1B,2A | 2 |
| 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A | 4 |
| 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A | 6 |
| 2021deg001 | 디자인기초 | 황희 | 2 | 3 | 목1A,1B,2A,2B | 2 |
| 2021deg002 | 색채이론 | 신사임당 | 3 | 2 | 금1A,1B,2A,2B | 2 |
| 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B | 4 |
+------------+----------------+--------------+----------+---------+----------------------+-----------+
--2. attend에 수강신청을 하면 course의 co_degree가 자동 증가하는 트리거
drop trigger if exists insert_attend;
delimiter $$
create trigger insert_attend after insert on attend
for each row
begin
update course set
co_degree = co_degree + 1
where co_code = new.at_co_code;
end $$
delimiter ;
mysql> insert into attend(at_std_num, at_co_code) values('2023160001', '2020ipc001');
Query OK, 1 row affected (0.01 sec)
mysql> select * from course;
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| co_code | co_name | co_professor | co_point | co_time | co_timetable | co_degree |
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| 2020ipc001 | 컴퓨터개론 | 유관순 | 2 | 2 | 화1A,1B,2A,2B | 3 |
| 2020ipc002 | 기초전기 | 이순신 | 3 | 4 | 월1A,1B,2A목1A,1B,2A | 2 |
| 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A | 4 |
| 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A | 6 |
| 2021deg001 | 디자인기초 | 황희 | 2 | 3 | 목1A,1B,2A,2B | 2 |
| 2021deg002 | 색채이론 | 신사임당 | 3 | 2 | 금1A,1B,2A,2B | 2 |
| 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B | 4 |
+------------+----------------+--------------+----------+---------+----------------------+-----------+
7 rows in set (0.00 sec)
mysql> select * from attend;
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repitition | at_score |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 1 | 2020160001 | 2020msc001 | 2024 | 1 | 30 | 38 | 8 | 9 | n | B |
| 2 | 2020160002 | 2020msc001 | 2024 | 1 | 40 | 40 | 10 | 10 | n | A |
| 3 | 2023160002 | 2021deg001 | 2024 | 1 | 39 | 39 | 9 | 8 | n | A |
| 4 | 2023160002 | 2021deg002 | 2024 | 1 | 37 | 20 | 9 | 1 | n | D |
| 5 | 2019160123 | 2020msc002 | 2024 | 1 | 22 | 11 | 1 | 2 | y | F |
| 6 | 2019456001 | 2020msc002 | 2024 | 1 | 10 | 15 | 2 | 3 | y | F |
| 7 | 2020123001 | 2020ipc001 | 2024 | 1 | 25 | 8 | 5 | 9 | y | F |
| 8 | 2020123020 | 2020ipc001 | 2024 | 1 | 21 | 35 | 4 | 5 | n | D |
| 9 | 2020123020 | 2022che001 | 2024 | 1 | 23 | 26 | 6 | 6 | n | D |
| 10 | 2020123001 | 2022che001 | 2024 | 1 | 31 | 24 | 7 | 7 | n | D |
| 11 | 2019456001 | 2020ipc002 | 2024 | 1 | 30 | 30 | 8 | 7 | n | C |
| 12 | 2019160123 | 2020ipc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 13 | 2022123001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 14 | 2020160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 15 | 2019160123 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 16 | 2019456001 | 2020msc001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 17 | 2023160002 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 18 | 2022123001 | 2022che001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 19 | 2023160001 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 20 | 2022123002 | 2021deg001 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 21 | 2022123002 | 2021deg002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 22 | 2023160002 | 2020msc002 | 2024 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 27 | 2023160001 | 2020ipc001 | NULL | NULL | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
--3. update 트리거 생성
--attend 값이 변경되면 co_degree의 값도 같이 변경되도록 트리거 작성
drop trigger if exists update_attend;
delimiter $$
create trigger update_attend after update on attend
for each row
begin
update course set
co_degree = (
case
when co_code = new.at_co_code then co_degree+1
when co_code = old.at_co_code then co_degree-1
else co_degree
end
);
end $$
delimiter ;
update attend set at_co_code = '2020ipc002'
where at_num=27;
mysql> select * from course;
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| co_code | co_name | co_professor | co_point | co_time | co_timetable | co_degree |
+------------+----------------+--------------+----------+---------+----------------------+-----------+
| 2020ipc001 | 컴퓨터개론 | 유관순 | 2 | 2 | 화1A,1B,2A,2B | 2 |
| 2020ipc002 | 기초전기 | 이순신 | 3 | 4 | 월1A,1B,2A목1A,1B,2A | 3 |
| 2020msc001 | 대학수학기초 | 홍길동 | 3 | 3 | 월1A,1B,2A수1A,1B,2A | 4 |
| 2020msc002 | 프로그래밍일반 | 임꺽정 | 3 | 3 | 월1A,1B,2A목1A,1B,2A | 6 |
| 2021deg001 | 디자인기초 | 황희 | 2 | 3 | 목1A,1B,2A,2B | 2 |
| 2021deg002 | 색채이론 | 신사임당 | 3 | 2 | 금1A,1B,2A,2B | 2 |
| 2022che001 | 화학이론 | 김길동 | 3 | 2 | 월1A,1B,수2A,2B | 4 |
+------------+----------------+--------------+----------+---------+----------------------+-----------+'백엔드 > MySQL' 카테고리의 다른 글
| MySQL Procedure (2) | 2024.09.03 |
|---|---|
| MySQL Trigger(1) (3) | 2024.09.03 |
| MySQL LEFT JOIN 활용 (0) | 2024.09.03 |
| MySQL VIEW 생성 (0) | 2024.09.03 |
| MySQL INDEX (0) | 2024.09.03 |