[SQL] Query | Trigger 이슈가 생기다.

1. CASE

문의사항이 하나 들어왔다. 신청 건마다 일자를 클릭하면 어떤 작업을 했는지 시간과 내용 등 로그 이력을 볼 수 있었는데 특정 기간의 신청 건들에서 동일한 날짜로 빈값이 들어있다는 것이었다.

확인해 보니, 해당 값은 약 10년 치 데이터에 들어가 있는 상황이었고 생각보다 큰 범위에 적지 않아 당황했다. 원인은 결재를 승인할 시 기본 상태와 더불어 보고서의 상태도 변경이 될 수 있도록 하는 기능을 반영하는 과정에서 이상값이 들어간 걸로 파악되었다.

해당 이상값은 가비지 데이터로 판명이 되어 삭제하는 걸로 일단락이 되었지만 대체 어떤 것때문에 이런 일이 나타난 건지 궁금했고, 어떤 조치를 취할 수 있을까 생각해 봤다.

 

2. Find the Cause + Solution

상태를 변경할 때 사용되는 테이블에 INSERT와 UPDATE에 대한 트리거가 걸려 있었다.

해당 테이블은 거의 모든 기능과 연관되는 주 테이블이었고, 기능을 반영하는 과정에서 UPDATE가 실행되어 반영한 날 기준으로 불필요한 데이터가 들어간 것이었다.

이럴 경우에는 해결 방법으로 ①트리거 조건 제한 ②트리거 분리 또는 삭제 ③테이블 분리 등을 생각해 볼 수 있다.

해당 프로젝트는 이미 운영되고 있는 프로젝트였기 때문에 쉽게 트리거를 변경하거나 테이블을 분리할 수가 없다. 그래서 기존 트리거에 조건을 추가해서 정말 의미 있는 UPDATE 일 때만 로그를 기록하도록 바꾸는 게 좋아 보인다.

 

[ 변경 전 ]

BEFORE UPDATE

   ON PKG1.TABLE1

   FOR EACH ROW

 

DECLARE

    v_Id         NUMBER (10, 0);

    v_NewState   VARCHAR2 (300);

    v_OldState   VARCHAR2 (300);

BEGIN

    SELECT :OLD.ID, :OLD.STATE

      INTO v_Id, v_OldState

      FROM DUAL;

 

    SELECT :NEW.STATE INTO v_NewState FROM DUAL;

 

    IF (v_NewState <> v_OldState)

    THEN

        BEGIN

            INSERT INTO PubDiagRqLog (Id,

                                      WritedDate,

                                      NewState,

                                      OldState)

                 VALUES (v_Id,

                         SYSDATE,

                         v_NewState,

                         v_OldState);

        END;

    END IF;

END;

 

[ 변경 후 ]

BEFORE UPDATE

   ON PKG1.TABLE1

   FOR EACH ROW

 

WHEN (

    -- STATE 값이 바뀌었고

    OLD.STATE IS DISTINCT FROM NEW.STATE AND

    -- 변경된 STATE가 NULL이 아니며

    NEW.STATE IS NOT NULL AND

    -- 특정 조건을 만족하는 경우만 기록

    NEW.STATE IN ('COMPLETED', 'FAILED', 'CANCELLED')

)

 

DECLARE

BEGIN

    INSERT INTO PubDiagRqLog (

        Id,

        WritedDate,

        NewState,

        OldState

    ) VALUES (

        :OLD.ID,

        SYSDATE,

        :NEW.STATE,

        :OLD.STATE

    );

END;

 

3. Reviewww

저번 포스팅에서 트리거에 대한 내용을 다룬 적이 있었는데, 이번 이슈로 인해 테이블 자체에 트리거를 걸어 이력을 남기는 용으로 사용할 수 있다는 걸 알게 되었다. 이번에는 해당 이상값을 삭제하는 걸로 해결할 수 있어 다행이었지만 어떤 기능을 구현하기 전에는 그에 따르는 영향을 생각할 수 있는 범위가 넓어지는 게 실력이 늘어나는 것이 아닐까 생각이 든다.