oracle 테이블 변경(alter table) 후 트리거 invalid 상태 변화

DBMS : Oracle 19.3 - 트리거 관련된 테이블 속성 변경 시 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'
;
  2    3  
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.

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>