[ORACLE] 테이블 변경 시 트리거(trigger) 상태 변화 테스트

트리거와 관련있는 테이블의 컬럼 변경 시 invalid 상태로 변경되지만 정상작동 가능하면 자동으로 valid 상태로 변경됨

테스트 환경 설정

-- 소스/타겟 테이블 생성
SQL> CREATE TABLE sk.t1 (c1 varchar(100), c2 varchar(200)) ;
Table created.

SQL> CREATE TABLE sk.t1_his (c1 varchar(100), c2 varchar(200),flag varchar(10)) ;
Table created.

-- 트리거 생성
SQL> create or replace trigger sk.tri1
AFTER INSERT OR UPDATE OR DELETE ON sk.t1
FOR EACH ROW
BEGIN
if inserting then
insert into sk.t1_his (
c1
,c2
, flag
) values (
:new.c1
,:new.c2
, 'I'
);
elsif updating then
insert into sk.t1_his (
c1
,c2
, flag
) values (
:new.c1
,:new.c2
, 'U'
);
elsif deleting then
insert into sk.t1_his (
c1
,c2
, flag
) values (
:old.c1
,:old.c2
, 'D'
);
end if;
end;
/

Trigger created.

트리거와 관련 없는 컬럼 추가

-- 트리거 상태 확인
SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       VALID

-- 트리거와 관련없는 컬럼 추가
SQL> ALTER TABLE sk.t1_his ADD (c3 varchar(100)) ;
Table altered.

-- 상태 변경 없음
SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;  

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       VALID

-- 정상 작동
SQL> INSERT INTO sk.t1 values('1','a') ;

1 row created.

SQL> SELECT * FROM sk.t1 ;

C1			       C2
------------------------------ ------------------------------
1			       a

SQL> SELECT * FROM sk.t1_his ;

C1			       C2			      FLAG	 C3
------------------------------ ------------------------------ ---------- ---------------------------------------------------------
1			       a			      I

SQL> rollback ;

Rollback complete.

트리거에서 사용되는 컬럼 삭제

-- 트리거 관련 컬럼 삭제
SQL> ALTER TABLE sk.t1_his DROP (c3) ;

Table altered.

SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       VALID

SQL> ALTER TABLE sk.t1_his DROP (c2) ;

Table altered.

-- ivalid 상태로 변경됨
SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       INVALID

-- 에러 발생
SQL> INSERT INTO sk.t1 values('1','a') ;
INSERT INTO sk.t1 values('1','a')
               *
ERROR at line 1:
ORA-04098: trigger 'SK.TRI1' is invalid and failed re-validation

-- 관련 컬럼 재생성
SQL> ALTER TABLE sk.t1_his ADD (c2 varchar(100))
;

Table altered.

SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       INVALID

-- 정상 작동함
SQL> INSERT INTO sk.t1 values('1','a') ;

1 row created.

SQL> SELECT * FROM sk.t1 ;

C1			       C2
------------------------------ ------------------------------
1			       a

SQL> SELECT * FROM sk.t1_his ;

C1			       FLAG	  C2
------------------------------ ---------- ------------------------------
1			       I	  a

SQL> rollback ;

Rollback complete.

-- 상태 자동으로 변경됨
SQL> SELECT object_name, status FROM DBA_objects
WHERE object_name = 'TRI1'
;

OBJECT_NAME		       STATUS
------------------------------ -------
TRI1			       VALID

SQL>