1. SQL 트리거를 사용하려면?
- OS/400 V5R1 이상
- Database Fixpak SF99501 설치
주) Fixpak 설치시 DSPDTAARA SF99501을 실행하면 아래와 같이 나타납니다.
Value
Offset *...+....1....+....2....+....3....+....4....+....5
0 'Group PTF#: SF99501-03 V5R1M0 August 22, 2001 '
2. 예제 프로그램 (SQL)
급여테이블(qgpl/emp)의 급여(salary) 값이 변경되면 그 변경 내역을
감사종적(Audit Trail)을 위한 테이블(qgpl/emplog)에 기록한다.
2.1 Source
CREATE TRIGGER qgpl.emptrail (1)
AFTER UPDATE OF salary ON qgpl.emp (2)
REFERENCING NEW ROW AS NR (3)
OLD ROW AS OR
FOR EACH ROW (4)
MODE DB2SQL (5)
WHEN (NR.salary > OR.salary) (6)
BEGIN (7)
INSERT INTO qgpl.emplog (EMPID, NEWSALARY, OLDSALARY, CHGDATE)
VALUES (NR.empid, NR.salary, OR.salary, CURRENT TIMESTAMP);
END
2.2 주요부분 해설
1) 트리거명 emptrail을 qgpl 라이브러리에 생성한다.
2) 트리거는 emp 테이블의 salary 컬럼이 변경되었을 때만 실행된다.
- emp 테이블의 레코드값이 변경되었을 때 실행되게 하려면
AFTER UPDATE ON qgpl.emp
- salary 또는 empdate 컬럼이 변경되었을 때 실해되게 하려면
AFTER UPDATE OF salary, empdate ON qgpl.emp
트리거 실행 시점은 각각의 변경 조건 전후로 6가지 조합 가능
3) 레코드값의 변경전/후에 대한 이미지 레코드를 선언한다.
4) 트리거 action 을 실행하는 방법을 지정한다.
- FOR EACH ROW : 각각의 Row 오퍼레이션에 대해서 실행
- FOR EACH STATEMENT : 각각의 Statement에 대해서 실행
5) 트리거의 Fired 시점을 지정한다.
- DB2ROW : 각각의 Row 오퍼레이션 실행후 Fired
- DB2SQL : 모든 Row에 대해 오퍼레이션 실행후 Fired
6) 트리거의 실행부분(Body)이 실행될 조건을 지정한다.
조건에 관계없이 실행되게 하려면 WHEN 문을 삭제
7) 트리거의 실행부분(Body)은 BEGIN 으로 시작해서 END 로 끝이난다.
실행부분은 SPL(Stored Procedure Language)을 이용하여 작성
3. Triggers 생성 및 삭제
1) 트리거 생성 (RUNSQLSTM 명령 이용)
RUNSQLSTM SRCFILE(qgpl/qsqlsrc) SRCMBR(emptrail) _
NAMING(*SQL) DBGVIEW(*LIST) OUTPUT(*PRINT)
isql 세션이나, OpNav를 이용하여 작성할 수도 있습니다.
2) 트리거 삭제 (isql 세션에서 실행)
DROP TRIGGER qgpl/emptrail
4. Triggers FAQ
트리거가 걸린 테이블을 삭제하려면?
☞ 로지컬이나 인덱스와는 달리 트리거는 삭제시 종속성 확인을 하지
않습니다. 테이블을 삭제하면 해당 트리거들도 같이 삭제가 됩니다.
테이블을 삭제 후 다시 생성시키면 트리거는?
☞ 테이블을 삭제하면 해당 트리거들도 함께 삭제 됩니다. 그러므로
테이블 생성 후에는 트리거도 다시 생성 시켜 주어야 합니다.
트리거가 걸린 테이블의 필드를 추가 또는 변경 하려면?
☞ SQL ALTER TABLE 명령을 이용하여 테이블을 변경하면 관련된 트리거는
아무런 변경없이 그대로 사용할 수가 있습니다.
(ALTER TABLE 사용법은 "SQL Basic" 강의를 참고 바랍니다.)
"Trigger program or external procedure detected an error" 메세지?
☞ 해당 테이블에 걸려 있는 트리거 프로그램에서 오류가 발생한 경우 입니다.
트리거 프로그램의 로직을 확인해 보세요.
(이 때 SQLCODE = -443 으로 세팅 됩니다)
[ 참고자료 ]
- IBM Redbook SG24-6503-00 Stored Procedures and Triggers
