정보처리기사 실기 - SQL 응용 : 트리거 작성

정보처리기사 실기 - SQL 응용 : 트리거 작성

 

트리거

 

• 특정 테이블에 삽입, 수정, 삭제 등 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램
• 이벤트는 전체 트랜잭션 대상과 각 행에 의해 발생되는 경우 모두를 포함, 테이블과 뷰(View), DB 작업을 대상으로 정의

 

① 트리거 목적

 

• 특정 테이블에 대한 데이터 변경을 시작점으로 설정하고 관련 작업 자동적 수행
• 일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용
• 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리

② 트리거 구성

 

• 다른 절차형SQL와 기본적 문법은 같음
• DML을 주된 목적으로 한다는 점에서는 프로시저와 비슷함
• EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지하고 외부 변수 IN, OUT이 없어서 프로시저나 사용자 정의함수와 상이

 

③ 트리거 문법

CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE/AFTER 트랜잭션타입 ON 테이블명
[FOR EACH ROW]
BEGIN
...
	[SQL]
	[CONTROL]
...
	[EXCEPTION]
...
END;

• 대상 테이블(TABLE_NAME)에 벌어진 이벤트 유형(TRANSACTION TYPE, INSERT/UPDATE/DELETE) 및 이벤트 순서(BEFORE/AFTER)에 맞게 트리거 수행을 위한 조건 입력
• ⁠매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어(FOR EACH ROW) 정의
• ⁠트리거에는 레코드 구조체라는 개념이 존재하는데, 대상 테이블의 데이터 변경을 이벤트 로 처리하기 때문에 변경 전후의 데이터값을 각각 구분할 때 사용
• ⁠P언어에서는 :OLD, :NEW로 사용되며 T언어에서는 deleted와 inserted로 사용
• ⁠각각 이전․이후 데이터를 의미하며 INSERT 시 이전 값은 NULL, DELETE 시 이후 값이 NULL을 의미

 

④ 트리거 예제

CREATE OR REPLACE TRIGGER PUT_EMPLOYEE_INFO_HISTORY --직원 정보 이력 입력
AFTER UPDATE -- 수정 후
ON EMPLOYEE_INFO_T -- 직원 정보 테이블을 대상으로 함
FOR EACH ROW -- ROW 단위 건별로 적용

BEGIN
	INSERT INTO EMPLOYEE_INFO_H -- 직원 정보 이력 테이블
		(EMPLOYEE_ID, SEQ_VAL,EMPLOYEE_NAME,EMPLOYEE_DEPT)
		( :OLD.EMPLOYEE_ID, -- 기존 ID
		SEQ_VAL.NEXT_VAL, -- 이력 순번
		:NEW.EMPLOYEE_NAME, -- 신규 성명
		:NEW.EMPLOYEE_DEPT); -- 신규 부서
END;

• 직원 정보(EMPLOYEE_INFO_T) 테이블 수정 후(AFTER UPDATE) 실행
• 직원 정보 이력(EMPLOYEE_INFO_H) 테이블에 데이터 삽입(INSERT)
• 기존 직원 ID(:OLD.EMPLOYEE_ID) 기준으로 기타 직원 정보는 변경된 정보(:NEW.) 삽입

 

⑤ 트리거 작성 시 주의사항

 

(1) 데이터 제어어(DCL, Data Control Language) 사용 불가 

 

• 트리거 내에는 COMMIT, ROLLBACK 등의 DCL이 사용안됨
• 쉽게 말하면 Auto Commit을 하는 결과와 같음
• 트리거 내에 COMMIT이나 ROLLBACK을 사용하고 컴파일 시 에러
• 트리거로 인해 발생하는 모든 후속 절차에도 동일 적용
• 트리거 내에서 타 프로시저를 호출하는 것도 가능한데 해당 프로시저 내에 COMMIT이 포함되어 있다면 이로 인해 오류 발생 가능

 

(2) 오류에 특히 주의

 

• 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 대부분 영향을 끼침
• 특정 테이블에 데이터를 추가한 후 발생하는 트리거에서 오류가 발생할 경우 트리거 이후의 작업이 진행되지 않고 데이터 추가마저 불가
• 결국 트랜잭션을 합치는 과정에서 더 높은 무결성 및 품질을 요구한다고 볼 수 있으므로 주의 필요