[SQL] 트리거 설정, 문자+숫자(ABC0001)로 아이디 주기

1. SQL 트리거

트리거(Trigger): 데이터베이스에서 특정 테이블에 INSERT, DELETE, UPDATE 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램.

전체 트랜잭션 작업에 대해 발생되는 트리거와, 각 행에 대해 발생되는 트리거가 있습니다.

[SQLD] 제2장 트리거(Trigger)란?

---

2. 트리거 생성 및 설정 방법

DELIMITER $$
CREATE TRIGGER [트리거명]
BEFORE INSERT ON [테이블명]
FOR EACH ROW
BEGIN
  [수행할 동작]
END$$
DELIMITER ;

---

3. 문자 + 숫자(ABC0001)로 아이디 주기

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

이렇게 하면 LHPL0001, LHPL0002로 아이디가 설정된다.

 

How to make MySQL table primary key auto increment with some prefix

 

[mysql] 문자열과 정수로 된 기본키 자동 증가 구현하기

---

4. 다른 테이블의 집계함수 정보 가져와서 필드에 연결

이 내용이 하이라이트이다. 사실 이 내용을 작성하려 앞의 트리거 내용을 기술했던 것이다.

문제. 각 직원은 동호회에 소속될 수 있다. 단, 동호회는 2개까지만 가입 가능하다.

 

어떻게 작성할 수 있을까?
소속이라는 테이블에 레코드가 추가될 때마다, CLUB_COUNT를 추가하고 싶다.
따라서 트리거를 다음과 같이 설정해보았다.

-- EMPLOYEE(EMPLOYEE_NUM, EMPLOYEE_NAME, CLUB_COUNT) -- 직원
-- CLUB(CLUB_NUM, CLUB_NAME, PRESIDENT) -- 동호회
-- BELONG(BELONG_NUM, MEMBER_NUM, CLUB_NUM) -- 동호회 소속

DELIMITER $$
CREATE TRIGGER tg_BELONG_insert
BEFORE INSERT ON BELONG
FOR EACH ROW
BEGIN
	UPDATE EMPLOYEE AS A
	SET A.CLUB_COUNT = (
				SELECT COUNT(B.CLUB_NUM)
				FROM BELONG AS B
				WHERE B.MEMBER_NUM = A.EMPLOYEE_NUM
				GROUP BY B.MEMBER_NUM
			);
END$$
DELIMITER ;

 

BELONG이라는 테이블에, 각 행에 INSERT 동작이 실행될 때마다
UPDATE 구문을 실행해주었다.
이때, EMPLOYEE 테이블의 CLUB_COUNT는 SELECT 하여 집계 함수 구문으로 가지고 왔다.

 

실행 결과는 다음과 같다.

-- EMPLOYEE(EMPLOYEE_NUM, EMPLOYEE_NAME, CLUB_COUNT)
1                 피카츄        1
2                 파이리        2
3                 꼬부기        0

 

트리거를 설정했으니, EMPLOYEE 테이블로 돌아가서 제약조건을 설정하면 된다.
제약조건은 다음과 같이 설정하였다.

CREATE TABLE EMPLOYEE_EJH(
	EMPLOYEE_NUM INT AUTO_INCREMENT PRIMARY KEY
	,EMPLOYEE_NAME VARCHAR(8) NOT NULL
	,CLUB_COUNT INT NOT NULL DEFAULT 0
	,CONSTRAINT C_count CHECK (CLUB_COUNT IN (0, 1, 2))
);

CLUB_COUNT는 NULL이 될 수 없으니 NULL을 제외하고 가능한 값(0, 1, 2)으로 제약 조건을 설정해주었다.

 

끝