[실습] 데이터베이스, 테이블, 트리거, 프로시저, 뷰
(1) 데이터베이스
CREATE DATABASE check_up;
(2) 테이블
CREATE TABLE team_info (
team_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) DEFAULT 'Unknown'
);
CREATE TABLE user_info (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
team_id INT,
email VARCHAR(50) UNIQUE,
age INT CHECK (age >= 18),
FOREIGN KEY (team_id) REFERENCES team_info(team_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
(3) 트리거
※ 트리거에서 사용되는 테이블은 기존에 생성되어 있어야 합니다.
CREATE TABLE logs (
num INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(20),
value VARCHAR(50),
action VARCHAR(10),
timestamp TIMESTAMP
);
DELIMITER //
CREATE TRIGGER user_insert_trigger
AFTER INSERT ON user_info
FOR EACH ROW
BEGIN
INSERT INTO logs (table_name, value, action, timestamp)
VALUES ('user_info', NEW.name, 'INSERT', NOW());
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER team_insert_trigger
AFTER INSERT ON team_info
FOR EACH ROW
BEGIN
INSERT INTO logs (table_name, value, action, timestamp)
VALUES ('team_info', NEW.name, 'INSERT', NOW());
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER user_delete_trigger
AFTER DELETE ON user_info
FOR EACH ROW
BEGIN
INSERT INTO logs (table_name, value, action, timestamp)
VALUES ('user_info', OLD.name, 'DELETE', NOW());
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER team_delete_trigger
BEFORE DELETE ON team_info
FOR EACH ROW
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE user_name VARCHAR(20);
DECLARE cur CURSOR FOR SELECT name FROM user_info WHERE team_id = OLD.team_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO user_name;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO logs (table_name, value, action, timestamp)
VALUES ('user_info', user_name, 'DELETE', NOW());
END LOOP;
CLOSE cur;
INSERT INTO logs (table_name, value, action, timestamp)
VALUES ('team_info', OLD.name, 'DELETE', NOW());
END //
DELIMITER ;
※ INSERT(삽입) 시, NEW, DELETE(삭제) 시, OLD 키워드를 사용해야 합니다.
(4) 프로시저
DELIMITER //
CREATE PROCEDURE insert_team(
IN name_value VARCHAR(50)
)
BEGIN
INSERT INTO team_info (name) VALUES (name_value);
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE insert_user(
IN name_value VARCHAR(20),
IN team_id_value INT,
IN email_value VARCHAR(50),
IN age_value INT
)
BEGIN
INSERT INTO user_info (name, team_id, email, age)
VALUES (name_value, team_id_value, email_value, age_value);
END //
DELIMITER ;
CALL insert_team(‘RED’);
CALL insert_team(‘BLUE’);
CALL insert_team(‘YELLOW’);
CALL insert_team(‘GREEN’);
CALL insert_user('yjyoo', 1, ‘yjyoo@pnpsecure.com', 24);
CALL insert_user('jhpark', 1, ‘jhpark@pnpsecure.com', 39);
CALL insert_user('slkim', 4, ‘slkim@pnpsecure.com', 26);
CALL insert_user('jhkim', 4, ‘jhkim@pnpsecure.com', 32);
CALL insert_user('sbcho', 4, ‘sbcho@pnpsecure.com', 29);
CALL insert_user('jhjeon', 2, ‘jhjeon@pnpsecure.com', 27);
CALL insert_user('jjlee', 2, ‘jjlee@pnpsecure.com', 30);
CALL insert_user('dyhan', 3, ‘dyhan@pnpsecure.com', 30);
CALL insert_user('jkjeng', 3, ‘jkjeng@pnpsecure.com', 25);
(5) 뷰
CREATE VIEW red_team AS SELECT * FROM user_info WHERE team_id = 1;
CREATE VIEW blue_team AS SELECT * FROM user_info WHERE team_id = 2;
CREATE VIEW yellow_team AS SELECT * FROM user_info WHERE team_id = 3;
CREATE VIEW green_team AS SELECT * FROM user_info WHERE team_id = 4;