[Oracle] Trigger

"Trigger (방아쇠를 당겨) 쏘다, 발사하다" 즉 지정한 조건을 만족하면 어떤 이벤트를 발생시키는 데이터베이스 객체

특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때,

데이터베이스에서 자동으로 동작하도록 작성된 프로그램

사용자가 직접 호출하는 것이 아니라, 데이터베이스에서 자동적으로 호출하는 것이 가장 큰 특징

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

트리거 생성 구문

 

CREATE [OR REPLACE] TRIGGER <트리거 이름>

{BEFORE|AFTER}<이벤트>ON<테이블>

[FOR EACH ROW]

[WHEN (<조건>)]

[DECLARE <변수선언>]

BEGIN

         <실행코드>

[EXCEPTION <예외사항>]

END <트리거 이름>;

 

 

ON<테이블> 에 들어간 대상 테이블은 트리거에서 작동될수없다.....  그래서 :OLD.컬럼명   :NEW.컬럼명 문법을 쓴다.

 

트랜잭션 일부로 처리되기떄문에 COMMIT, ROLLBACK, SAVEPOINT를 못쓴다.

 

OR REPLACE : 선택적인(Optional) 예약어임 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체

 

동작시기 : 트리거가 실행될 때를 지정합니다. 종류에는 AFTER와 BEFORE가 있음

AFTER : 테이블이 변경된 후에 트리거가 실행됨

BEFORE : 테이블이 변경되지 건에 트리거가 실행됨

동작 : 트리거가 실행되게 할 작업의 종류를 지정

  -종류에는 INSERT, DELETE, UPDATE가 있음

INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거가 실행됨

DELETE : 테이블의 튜플을 삭제할 때 트리거가 실행됨

UPDATE :  테이블의 튜플을 수정할 때 트리거가 실행됨

NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정함

 :NEW.컬럼명 : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미함

 :OLD.컬럼명 : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미함

· FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

· WHEN 조건식 : 선택적인(Optional) 예약어입니다. 트리거를 적용할 튜플의 조건을 지정함

· 트리거 BODY

 - 트리거의 본문 코드를 입력하는 부분

 - BEGIN으로 시작해 END로 끝나는데, 적어도 하나 이상의 SQL문이 필요함. 그렇지 않으면 오류가 발생

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

ERP 권한 히스토리 테이블 및 트리거

 

테이블생성

 

CREATE TABLE KUP.SYSACCESSPRG_HISTORY
(
  FUNCGB      VARCHAR2(1 BYTE)                  NOT NULL,
  UNITGB      VARCHAR2(3 BYTE)                  NOT NULL,
  PROG_ID     VARCHAR2(20 BYTE)                 NOT NULL,
  LOGIN_ID    VARCHAR2(10 BYTE)                 NOT NULL,
  PROG_NAME   VARCHAR2(50 BYTE),
  PROG_TEXT   CHAR(1 BYTE),
  CHANGEGB    CHAR(1 BYTE),
  CHANGETEMP  VARCHAR2(10 BYTE),
  CHANGEMD    TIMESTAMP,                            
  IPADRESS    VARCHAR2(20 BYTE)
)

 

권한이 삭제되거나 업데이트시 새로만든 테이블로 기록을 남기는 트리거 생성

 

CREATE OR REPLACE TRIGGER SYSACCESSPRG_HISTORY_CHK
AFTER DELETE OR UPDATE
ON SYSACCESSPRG
FOR EACH ROW
DECLARE
    T_IPADDR    VARCHAR2(15);
    T_TIME      TIMESTAMP(6);
BEGIN
    
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') 
    into T_IPADDR
    from DUAL;
    
    SELECT SYSDATE
    INTO T_TIME
    FROM DUAL;
   
   IF UPDATING THEN 
        
        INSERT INTO SYSACCESSPRG_HISTORY 
        VALUES
        (
        :NEW.FUNCGB
       ,:NEW.UNITGB
       ,:NEW.PROG_ID
       ,:NEW.LOGIN_ID
       ,:NEW.PROG_NAME
       ,:NEW.PROG_TEXT
       ,'U'
       ,:NEW.LASTEMP
       ,T_TIME
       ,T_IPADDR
        );  
   ELSIF DELETING THEN
        INSERT INTO SYSACCESSPRG_HISTORY 
        VALUES
        (
        :OLD.FUNCGB
       ,:OLD.UNITGB
       ,:OLD.PROG_ID
       ,:OLD.LOGIN_ID
       ,:OLD.PROG_NAME
       ,:OLD.PROG_TEXT
       ,'D'
       ,:OLD.LASTEMP
       ,T_TIME
       ,T_IPADDR      
        );
       
  END IF;

END SYSACCESSPRG_HISTORY_CHK;

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

비밀번호 5회이상 초과시 로그인 잠김용 테이블 및 트리거 

#파워빌더 스크립트에서 조건은 처리해주었음#

 

테이블생성

 

CREATE TABLE KUP.LOGIN_COUNT
(
  LOGIN_ID       VARCHAR2(10 BYTE)              NOT NULL,
  LOGIN_NAME     VARCHAR2(20 BYTE)              NOT NULL,
  TRY_COUNT      VARCHAR2(1 BYTE)               DEFAULT '0'
)

;

 

기존 로그인 테이블에서 새로만든 테이블로 0으로 세팅

 

INSERT INTO LOGIN_COUNT
SELECT LOGIN_ID
,LOGIN_NAME
,'0'
FROM LOGIN
;

 

로그인 테이블 데이터 생성시 로그인 카운트 테이블로 데이터 0 세팅하는 트리거 생성

 

CREATE OR REPLACE TRIGGER KUP.T_LOGIN_COUNT_TRY
AFTER INSERT
ON LOGIN
FOR EACH ROW
BEGIN

    IF INSERTING THEN 
        INSERT INTO LOGIN_COUNT
        VALUES
        (
            :NEW.LOGIN_ID,
            :NEW.LOGIN_NAME,
            '0'
        );
        
    END IF;

END T_LOGIN_COUNT_TRY;

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

퇴사시 ERP권한 자동삭제 트리거

 

CREATE OR REPLACE TRIGGER KUP.PER_MAST_RETDAT
AFTER UPDATE
ON PER_MAST
FOR EACH ROW
BEGIN


    IF UPDATING AND (:NEW.RETDAT IS NOT NULL) THEN  

    DELETE 
    FROM SYSACCESSPRG
    WHERE LOGIN_ID=:OLD.EMPCD
    ;


    DELETE 
    FROM LOGIN
    WHERE LOGIN_ID=:OLD.EMPCD
    ;
   
  END IF;
  
END PER_MAST_RETDAT;