[DATABASE] 트리거

트리거

• 특정 테이블에 INSERT, UPDATE, DELTE 등의 이벤트가 발생할 때 자동으로 실행되는 저장 프로시저
• 주로 데이터 무결성 유지, 로깅 및 감사 용도로 사용

트리거 특징

• 트리거는 DML과 같은 트랜잭션 안에서 실행되어 에러 발생시 전체 롤백 가능
• 보통 DML 실행 후 실행 되지만 일부 RDBMS에서는 실행시점 설정 가능 
• 변경 전 데이터와 변경 후 데이터를 모두 비교 가능 

트리거 장점

• 별도 코드 없이 DB 내부에서 자동으로 동작 (자동화)
• 무결성 보장 가능
• 변경 이력을 남기는데 적합(누가, 언제, 무엇을 바꿨는지)

트리거 단점

• 외부에서 보기 어렵고, 실행 시점도 명확하지 않아 디버깅이 어려움
• 과도한 트리거나 복잡한 로직은 DML 속도 저하 유발 가능성이 있음
• 트리거 안에서 또 다른 트리거를 호출할 경우 무한 루프 가능성 있음

트리거 기본 문법

CREATE TRIGGER [트리거이름]
ON [테이블이름]
AFTER [INSERT | UPDATE | DELETE]  -- 또는 INSTEAD OF
AS
BEGIN
    -- 트리거 실행 시 동작할 SQL 문
END;

트리거 예시

• 학생 테이블의 데이터를 INSERT, UPDATE, DELETE 할 때 로그 테이블에 로그를 쌓을려고 한다.

쿼리문

• 주의사항! 조건 분기를 INSERT를 먼저하면 UPDATE도 INSERT로 인식한다 
• 이유는 UPDATE도 inserted 테이블이 생성되니깐

CREATE TRIGGER trg_Student_log
ON STUDENT
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	-- UPDATE
	-- 새로 생성된 테이블과 이전 테이블을 비교해서 수정여부 확인
	IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) 
	BEGIN
		INSERT INTO LOG_TABLE (LOG_TABLE, LOG_DML, LOG_CONTENT, LOG_EMP)
		SELECT 
			'STUDENT', 
			'UPDATE', 
			'[기존] STUDENT_SCHOOL: ' + CAST(D.STUDENT_SCHOOL AS VARCHAR)
			 + ', STUDENT_NAME: ' +  CAST(D.STUDENT_NAME AS VARCHAR)
			 + ', STUDENT_AGE: ' +  CAST(D.STUDENT_AGE AS VARCHAR)
			 + ', STUDENT_CLASS: ' +  CAST(D.STUDENT_CLASS AS VARCHAR)
			 + ', JOB1: ' + CAST(D.JOB1 AS VARCHAR)
			 + ', JOB2: ' + CAST(D.JOB2 AS VARCHAR)
			 + ', JOB3: ' + CAST(D.JOB3 AS VARCHAR)
			 + '-> [변경] STUDENT_SCHOOL: ' + CAST(I.STUDENT_SCHOOL AS VARCHAR)
			 + ', STUDENT_NAME: ' + CAST(I.STUDENT_NAME AS VARCHAR)
			 + ', STUDENT_AGE: ' + CAST(I.STUDENT_AGE AS VARCHAR)
			 + ', STUDENT_CLASS: ' + CAST(I.STUDENT_CLASS AS VARCHAR)
			 + ', JOB1: ' + CAST(I.JOB1 AS VARCHAR)
			 + ', JOB2: ' + CAST(I.JOB2 AS VARCHAR)
			 + ', JOB3: ' + CAST(I.JOB3 AS VARCHAR),
			 ''
		  FROM inserted I INNER JOIN deleted D
			ON I.STUDENT_SEQ = D.STUDENT_SEQ
		 WHERE I.STUDENT_SCHOOL <> D.STUDENT_SCHOOL
		    OR I.STUDENT_NAME <> D.STUDENT_NAME
			OR I.STUDENT_AGE <> D.STUDENT_AGE
			OR I.STUDENT_CLASS <> D.STUDENT_CLASS
			OR I.JOB1 <> D.JOB1
			OR I.JOB2 <> D.JOB2
			OR I.JOB3 <> D.JOB3
	END

	-- INSERT
	-- inserted => 새로 생성된 테이블 
	ELSE IF EXISTS (SELECT * FROM inserted)	
	BEGIN
		INSERT INTO LOG_TABLE (LOG_TABLE, LOG_DML, LOG_CONTENT, LOG_EMP)
		SELECT 
			'STUDENT', 
			'INSERT' , 
			'STUDENT_SEQ [' + CAST(I.STUDENT_SEQ AS VARCHAR) + ']', 
			''
		  FROM inserted I
	END
    
	

	-- DELETE
	-- DELETE => 이전 테이블 
	ELSE IF EXISTS (SELECT * FROM deleted)
	BEGIN
		INSERT INTO LOG_TABLE (LOG_TABLE, LOG_DML, LOG_CONTENT, LOG_EMP)
		SELECT 
			'STUDENT', 
			'DELETE' , 
			'STUDENT_SEQ [' + CAST(D.STUDENT_SEQ AS VARCHAR) + ']', 
			''
		  FROM deleted D
	END
END;

결과

• LOG_EMP는 STUDENT에 UPDATE_EMP(수정자) 컬럼이 없어 그냥 공백으로 처리했다