[MySQL] Transaction과 Trigger

고봉진·2023년 2월 20일
0

TIL/SQL&DB

목록 보기
1/2

SQL - Advanced 01

  1. Transaction - 일관성
  2. Trigger - 무결성

Transaction

다 성공하던지 다 실패하던지 해야하는 여러 쿼리문(쪼개질 수 없는 업무처리의 단위)을 묶어서 하나의 작업처럼 처리하는 방법

  • e.g. 계좌이체
-- 기존 users 테이블 삭제
DROP TABLE IF EXISTS users;

-- 자동 COMMIT 비활성화
SET autocommit = 0;

-- users 테이블 생성
CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(10) NOT NULL
);

START TRANSACTION;
INSERT INTO users (name) VALUES ('봉진'), ('test');
SELECT * FROM users;

-- ROLLBACK;

COMMIT;

Triggers

어느 특정한 동작에 반응해 자동으로 필요한 동작을 실행하는 것(위키피디아)
-> INSERT, UPDATE, DELETE : 데이터베이스에 변화가 일어날 경우들 (DML의 영향을 받는 필드 값에만 적용할 수 있음)
이에 대해 무엇을 할지 지정하는 것. 특정 동작 전, 후에 실행시킬 수 있다.

  • e.g. 유저가 글을 썼을 때 다른 테이블에 유저가 글을 썼음을 기록
-- 사전 준비
DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
	id INT AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(100) NOT NULL,
	createdAt DATETIME NOT NULL,
	updatedAt DATETIME NOT NULL
);

INSERT INTO articles (title, createdAt, updatedAt)
VALUES ('title1', CURRENT_TIME(), CURRENT_TIME());

SELECT * FROM articles;

# "여기서부터 종료조건은 // 다" BEGIN ... END 사이에 여러가지 구문이 들어갈 때
DELIMITER // 
CREATE TRIGGER myTrigger
	BEFORE UPDATE  # 전이라고? 뭘 수정한다는거지?
	ON articles FOR EACH ROW
BEGIN
	SET NEW.updatedAt = CURRENT_TIME();   # 이걸 수정하다는거구나. 세미콜론 주의 -> DELIMITER //
	# ... 다른 작업들
END //
# "여기서는 다시 ;"
DELIMITER ;   

#################################
# DELIMITER 뒤에 주석이 있으면 안된다 #
#################################

SHOW TRIGGERS;   # 트리거 목록 확인
# DROP TRIGGER myTrigger;

UPDATE articles SET title = 'new title' WHERE id = '1';

OLD, NEW 키워드 사용할 수 있는 상황 :

OLDNEW
INSERTNOYES
UPDATEYESYES
DELETEYESNO

Practice #2

-- 사전 준비
CREATE TABLE articleLogs (
	id INT AUTO_INCREMENT PRIMARY KEY,
	description VARCHAR(100) NOT NULL,
	createdAt DATETIME NOT NULL
);

DROP TRIGGER IF EXISTS practiceTrigger;

DELIMITER //
CREATE TRIGGER practiceTrigger
	BEFORE INSERT   # AFTER 도 됨
	ON articles FOR EACH ROW
BEGIN
	INSERT INTO articleLogs (description, createdAt) VALUES ('글이 작성되었습니다.', CURRENT_TIME());
END //
DELIMITER ;

INSERT INTO articles (title, createdAt, updatedAt) VALUE ('작성1', CURRENT_TIME(), CURRENT_TIME);
SELECT * FROM articleLogs;

적용

  • 관리자페이지에서 개시글 관리
  • 백업
  • 삭제된 글 일정 기간 임시보관
  • 버그 로그 남기기
  • 댓글 작성 알림
  • 키워드 알림

Practice #2 심화

내 답안:

DELIMITER //
CREATE TRIGGER practiceTrigger2
	AFTER INSERT
	ON articles FOR EACH ROW
BEGIN
	INSERT INTO articleLogs (description, createdAt) VALUES (CONCAT((SELECT id FROM articles ORDER BY createdAt DESC LIMIT 1), '번 글이 작성되었습니다.'), CURRENT_TIME());
END //
DELIMITER ;

INSERT INTO articles (title, createdAt, updatedAt) VALUE ('작성1', CURRENT_TIME(), CURRENT_TIME);
SELECT * FROM articleLogs;

대안:

DROP TRIGGER IF EXISTS practiceTrigger2;

DELIMITER //
CREATE TRIGGER practiceTrigger2
	AFTER INSERT   # BEFORE일 경우 NEW 키워드 사용시 문제가 발생 ('0번 글이 작성되었습니다.')
	ON articles FOR EACH ROW
BEGIN
	INSERT INTO articleLogs (description, createdAt) 
	VALUES (CONCAT(NEW.id, '번 글이 작성되었습니다.'), CURRENT_TIME());   # 새로 쓰여진 글의 id : NEW.id로 추출 가능
END //
DELIMITER ;

INSERT INTO articles (title, createdAt, updatedAt) VALUE ('작성55', CURRENT_TIME(), CURRENT_TIME);
SELECT * FROM articleLogs;

Practice #3

-- 사전 준비
CREATE TABLE backupArticles (
	id INT AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(100) NOT NULL,
	createdAt DATETIME NOT NULL,
	updatedAt DATETIME NOT NULL
);

-- 같은 이름의 트리거가 이미 존재한다면 삭제
DROP TRIGGER IF EXISTS backupTrigger;

DELIMITER //
CREATE TRIGGER backupTrigger
	AFTER DELETE   # 일반적으로 AFTER (삭제된 후에)
	# BEFORE 키워드 사용시 지금 삭제된 글이 다음 DELETE 실행할 때 실행되는 것 같다.
	ON articles FOR EACH ROW
BEGIN
	INSERT INTO backupArticles (title, createdAt, updatedAt)
	VALUES (OLD.title, OLD.createdAt, OLD.updatedAt);
END //
DELIMITER ;

DELETE FROM articles WHERE id = '1';
SELECT * FROM backupArticles;

참고사항

Transaction 생성 후 정상적으로 종료되지 않아 발생하는 에러 발생시 해결법:

-- 실행중인 프로세스 목록 확인
SELECT * FROM information_schema.INNODB_TRX

-- 특정 프로세스의 trx_mysql_thread_id 삭제
KILL [trx_mysql_thread_id1];
profile
이토록 멋진 휴식!

0개의 댓글