오라클 트리거 로그테이블 만들기

트리거 정리 참조 : http://blog.naver.com/PostView.nhn?blogId=finekiller&logNo=70130220441&parentCategoryNo=&categoryNo=9&viewDate=&isShowPopularPosts=true&from=search

트리거 생성

SQL코드

CREATE OR REPLACE TRIGGER COMMON_TRIGGER 

AFTER INSERT OR UPDATE OR DELETE ON COMMON

FOR EACH ROW

BEGIN

IF INSERTING THEN 

  INSERT INTO COMMON_LOG(COMMON_LOG_SEQ,COMMON_LOW_SEQ,COMMON_BIG_SEQ,COMMON_BIG_NAME,COMMON_LOW_NAME,COMMON_INSERT_DATE,COMMON_UPDATE_DATE,COMMON_LOG_CHECK,COMMON_INSERT_DATE_LOG)

    VALUES(COMMON_LOG_SEQUENCE.NEXTVAL,:NEW.COMMON_LOW_SEQ,:NEW.COMMON_BIG_SEQ,:NEW.COMMON_BIG_NAME,:NEW.COMMON_LOW_NAME,:NEW.COMMON_INSERT_DATE,:NEW.COMMON_UPDATE_DATE,'I',sysdate);

  ELSIF UPDATING THEN 

  INSERT INTO COMMON_LOG(COMMON_LOG_SEQ,COMMON_LOW_SEQ,COMMON_BIG_SEQ,COMMON_BIG_NAME,COMMON_LOW_NAME,COMMON_INSERT_DATE,COMMON_UPDATE_DATE,COMMON_LOG_CHECK,COMMON_INSERT_DATE_LOG)

    VALUES(COMMON_LOG_SEQUENCE.NEXTVAL,:OLD.COMMON_LOW_SEQ,:OLD.COMMON_BIG_SEQ,:OLD.COMMON_BIG_NAME,:OLD.COMMON_LOW_NAME,:OLD.COMMON_INSERT_DATE,:OLD.COMMON_UPDATE_DATE,'O',sysdate);

    INSERT INTO COMMON_LOG(COMMON_LOG_SEQ,COMMON_LOW_SEQ,COMMON_BIG_SEQ,COMMON_BIG_NAME,COMMON_LOW_NAME,COMMON_INSERT_DATE,COMMON_UPDATE_DATE,COMMON_LOG_CHECK,COMMON_INSERT_DATE_LOG)

    VALUES(COMMON_LOG_SEQUENCE.NEXTVAL,:NEW.COMMON_LOW_SEQ,:NEW.COMMON_BIG_SEQ,:NEW.COMMON_BIG_NAME,:NEW.COMMON_LOW_NAME,:NEW.COMMON_INSERT_DATE,:NEW.COMMON_UPDATE_DATE,'U',sysdate);

  ELSIF DELETING THEN

  INSERT INTO COMMON_LOG(COMMON_LOG_SEQ,COMMON_LOW_SEQ,COMMON_BIG_SEQ,COMMON_BIG_NAME,COMMON_LOW_NAME,COMMON_INSERT_DATE,COMMON_UPDATE_DATE,COMMON_LOG_CHECK,COMMON_INSERT_DATE_LOG)

    VALUES(COMMON_LOG_SEQUENCE.NEXTVAL,:OLD.COMMON_LOW_SEQ,:OLD.COMMON_BIG_SEQ,:OLD.COMMON_BIG_NAME,:OLD.COMMON_LOW_NAME,:OLD.COMMON_INSERT_DATE,:OLD.COMMON_UPDATE_DATE,'D',sysdate);

 END IF;                

END;

COMMON_TRIGGER => 트리거 이름

COMMON => 트리거 적용 테이블

COMMON_LOG => 트리거 적용시 저장 로그 테이블

트리거 에러 확인

select * from user_errors where type = 'TRIGGER'

트리거 권한 추가

grant create any trigger to 유저;