Trigger를 이용한 변경 전 데이터 보관하기

TRG_USER.TRG

CREATE OR REPLACE TRIGGER TRG_USER
  AFTER UPDATE OR DELETE ON TB_USER FOR EACH ROW
DECLARE
    V_DML_STS     VARCHAR2(1);		-- DML 요청 상태(UPDATE : U, DELETE : D)
    V_DML_DATE    DATE;				-- DML 요청 시간
    V_DML_IP      VARCHAR2(15);		-- DML 요청 IP
    V_DML_PRGM    VARCHAR2(100);	-- DML 요청 프로그램
    V_DML_MACHINE VARCHAR2(50);		-- DML 요청 MACHINE
    
BEGIN
  
    V_DML_IP      := SYS_CONTEXT('USERENV','IP_ADDRESS');
    V_DML_PRGM    := SUBSTRB(SYS_CONTEXT('USERENV','MODULE'), 1, 100);
    V_DML_DATE    := SYSDATE;
    V_DML_MACHINE := SUBSTRB(SYS_CONTEXT('USERENV','HOST'), 1, 50);

    IF UPDATING THEN
        V_DML_STS := 'U';
    ELSIF DELETING THEN
        V_DML_STS := 'D';
    ELSE
        V_DML_STS := 'O';
    END IF;
    
    INSERT INTO TRG_USER (
                USER_ID,
                DML_DATE,
                DML_STS,
                DML_IP,
                DML_PRGM,
                DML_MACHINE,
                USER_NM,
                CRE_USER_ID,
                CRE_DATE,
                UPD_USER_ID,
                UPD_DATE
    ) VALUES (
                :OLD.USER_ID,
                V_DML_DATE,
                V_DML_STS,
                V_DML_IP,
                V_DML_PRGM,
                V_DML_MACHINE,
                :OLD.USER_NM,
                :OLD.CRE_USER_ID,
                :OLD.CRE_DATE,
                :OLD.UPD_USER_ID,
                :OLD.UPD_DATE
    );
      
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
            
END TRG_USER;