됩니다만...

TAB는 한개의 레코드만 있는 테이블인가요?

예를 들어

TAB 테이블에

A B C 컬럼의 값이

a b c 라고 한다면

a b c에서

b b c로 바뀌면

TAB_HISTORY에는

1 a b

이런 행이 생기고

다시 TAB이

b c c로 바뀌면

2 b c가 생기고

다시 TAB이

a c c로 바뀌면

1 b a가 생기고

다시 TAB이 b c c가 되면

1 a b가 생깁니까?

그러면 결과적으로 TAB_HISTORY는

1 a b

2 b c

1 b a

1 a b

이런 식이 됩니까?

그러면 1 a b가 중복되며

어떤 순서로 바뀌었는지도 알 수 없고

TAB에 한행(예를 들어 b b b같은 행)이 생기면...

그때는 TAB_HISTORY에는 어떤 데이타가 생겨야 하는 거죠?

그리고 그 렇게 행이 생긴 것과 기존 행이 변경된 것을 구분하는 방법은 어떤거죠?

만약 행이 삭제된다면 또 어떻게 표현되어야 하죠?

우선 답변 너무나 감사드립니다 ~ 꾸벅

TAB테이블은 여러 row가 있구요

예를 들어

TAB 테이블에

A B C 컬럼의 값이

a b c 라고 한다면

a b c-->b b c이면

TAB_HISTORY에는

1 a b 이렇게 들어갑니다.

(1은 첫번째 컬럼이란 의미고, 두번째 a는 before값, 세번째 b는 after값이죠)

마찬가지로

이런 행이 생기고

다시 TAB이

b b c --> b d c 이면

TAB_HISTORY에는

2 b d 이렇게 들어갑니다.

row 앞에 현날짜를 박아 줄거고, row_id가 있어서 괜찮습ㄴ디ㅏ.

행이 삭제 되는 건 별도로 세팅할 거구요

어케 해야 할까요? -.-

예 무슨 말씀이신지 알겠구요

네가지 정도가 이슈가 되겠네요.

1. 날짜만으로는 히스토리가 중복되므로 일종의 transaction-번호 역할을 하는 시퀀스를 하나 만들어서 사용하셔야 하구요..

2. 이런 식으로 히스토리 관리를 해야 하는 테이블이 많다면 일일이 트리거를 만들어 주는 작업은 단순 노가다가 되므로(코딩양도 엄청 많습니다.) 테이블 명과 히스토리 테이블 명을 입력하면 트리거를 만들어주는 SP를 하나 만들어 두시는 것이 좋을 것이구요.

3. rowid를 사용하는 경우는 insert시에는 before 트리거에서는 검출할 수 없으니 after 트리거를 사용하여야 한다.

4. 이전 값 및 변경값에 들어갈 수 있는 컬럼의 데이타 타입이 다양할 수 있으므로 이 컬럼에 들어갈 수 있는 데이타 타입에는 일부의 제약을 가한다.(예를 들어 to_char로 바꿀때 varchar2(4000) 까지만 히스토리에 저장한다... 이런식으로)

이상의 것을 유념하시면 트리거를 만드실 수 있습니다.

2번의 트리거를 만드는 프로시져는 all_tab_columns같은 시스템 뷰를 사용하시면 쉽게 구현하실 수 있습니다.

필요하시면 샘플을 하나 만들어서 올려드리도록 하겠습니다.

그런데요..

히스토리 테이블을 원본 테이블과 똑같은 컬럼모양으로 만들고 트랜잭션 번호를 키에 하나 추가하고 변경구분(이전값의 행,이후값의 행)

이렇게 만드시는 것이 더 쉽지 않나요?

아니면 님의 말씀과는 달리

이 히스토리 테이블이 특정 테이블의 히스토리만이 아닌 모든 테이블(또는 많은 테이블)의 히스토리를  한 히스토리 테이블에 몰아 넣기 위한 방법이신가요?

앗!!! 샘플 필요해요~ 이렇게 감사할 수가

저도 님이 말씀하신 방법으로 할려고 했는데, 현업이 어떤 컬럼이 변경되었는지로 보고 싶다고 해서요.

만약 history 테이블이 다음과 같은 형태를 갖는다면

SQL> desc tab_hist
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 R_ID                                               ROWID
 TR_NO                                              NUMBER
 COLUMN_ID                                          NUMBER
 BF_VALUE                                           VARCHAR2(4000)
 AF_VALUE                                           VARCHAR2(4000)
 TR_TAG                                             VARCHAR2(1)
 CHG_DT                                             DATE

tr_no는 트랜잭션 번호이구요

트랜잭션 번호용 시퀀스가 sq_tr_no라고 한다면

대략

create or replace trigger trg_tab_org
after insert or update on tab_org
for each row
declare
begin
   If inserting then
      insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)
      values ( :new.rowid,sq_tr_no.nextval,1,:new.a,'I',sysdate);
      insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)
      values ( :new.rowid,sq_tr_no.nextval,2,:new.b,'I',sysdate);
      insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)
      values ( :new.rowid,sq_tr_no.nextval,3,:new.c,'I',sysdate);
   elsif updating then
      if :new.a = :old.a then
         null;
      else
         insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
         values ( :new.rowid,sq_tr_no.nextval,1,:old.a,:new.a,'U',sysdate);
      end if;
      if :new.b = :old.b then
         null;
      else
         insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
         values ( :new.rowid,sq_tr_no.nextval,2,:old.b,:new.b,'U',sysdate);
      end if;
      if :new.c = :old.c then
         null;
      else
         insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
         values ( :new.rowid,sq_tr_no.nextval,3,:old.c,:new.c,'U',sysdate);
      end if;
   end if;
end;
/

이런 형태의 트리거가 됩니다...

그러면 이런형태의 트리거를 자동으로 만들어주는 프로시져는

다음과 같은 형태이구요

create or replace procedure pr_make_trigger_syntax
(
   ar_owner           varchar2,
   ar_table_name      varchar2,
   ar_trigger_name    varchar2,
   ar_sequence_name   varchar2
)
is
   type r_rec is record
   (
      column_name       all_tab_columns.column_name%type,
      column_id         all_tab_columns.column_id%type
   );
   type t_rec is table of r_rec
      index by binary_integer;
   lt_rec         t_rec;
   lr_rec         r_rec;
begin
   dbms_output.enable;
   for lrA in ( select column_name,column_id from all_tab_columns where owner = ar_owner and table_name = ar_table_name
                  order by column_id)
   loop
      lr_rec.column_name := lrA.column_name;
      lr_rec.column_id := lrA.column_id;
      lt_rec(lt_rec.count + 1) := lr_rec;
   end loop;
   dbms_output.put('create or replace trigger ');
   dbms_output.put_line(ar_owner ||'.'||ar_trigger_name);
   dbms_output.put('after insert or update on ');
   dbms_output.put_line(ar_owner ||'.'||ar_table_name);
   dbms_output.put_line('for each row');
   dbms_output.put_line('declare');
   dbms_output.put_line('begin');
   dbms_output.put_line(chr(9)||'If inserting then');
   for lii in lt_rec.first..lt_rec.last loop
      dbms_output.put_line(chr(9)||chr(9)||'insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)');
      dbms_output.put(chr(9)||chr(9)||chr(9)||'values ( :new.rowid,');
      dbms_output.put(ar_sequence_name||'.nextval,');
      dbms_output.put(lt_rec(lii).column_id);
      dbms_output.put(',');
      dbms_output.put('to_char(:new.');
      dbms_output.put(lt_rec(lii).column_name);
      dbms_output.put_line('),''I'',sysdate);');
   end loop;
   dbms_output.put_line(chr(9)||'elsif updating then');
   for lii in lt_rec.first..lt_rec.last loop
      dbms_output.put_line(chr(9)||chr(9)||'if :new.'||lt_rec(lii).column_name||' = :old.'||lt_rec(lii).column_name||' then');
      dbms_output.put_line(chr(9)||chr(9)||chr(9)||'null;');
      dbms_output.put_line(chr(9)||chr(9)||'else');
      dbms_output.put_line(chr(9)||chr(9)||chr(9)||'insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)');
      dbms_output.put(chr(9)||chr(9)||chr(9)||chr(9)||'values ( :new.rowid,');
      dbms_output.put(ar_sequence_name||'.nextval,');
      dbms_output.put(lt_rec(lii).column_id);
      dbms_output.put(',');
      dbms_output.put('to_char(:old.');
      dbms_output.put(lt_rec(lii).column_name);
      dbms_output.put('),');
      dbms_output.put('to_char(:new.');
      dbms_output.put(lt_rec(lii).column_name);
      dbms_output.put_line('),''U'',sysdate);');
      dbms_output.put_line(chr(9)||chr(9)||'end if;');
   end loop;
   dbms_output.put_line(chr(9)||'end if;');
   dbms_output.put_line('end;');
end;
/
테스트 해보면

SQL> exec pr_make_trigger_syntax('CALLUSER','TAB_ORG','TRG_TAB_ORG','SQ_TR_NO');
create or replace trigger CALLUSER.TRG_TAB_ORG                                 
after insert or update on CALLUSER.TAB_ORG                                     
for each row                                                                   
declare                                                                        
begin                                                                          
 If inserting then                                                             
  insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)          
   values ( :new.rowid,SQ_TR_NO.nextval,1,to_char(:new.A),'I',sysdate);        
  insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)          
   values ( :new.rowid,SQ_TR_NO.nextval,2,to_char(:new.B),'I',sysdate);        
  insert into tab_hist ( r_id,tr_no,column_id,af_value,tr_tag,chg_dt)          
   values ( :new.rowid,SQ_TR_NO.nextval,3,to_char(:new.C),'I',sysdate);        
 elsif updating then                                                           
  if :new.A = :old.A then                                                      
   null;                                                                       
  else                                                                         
   insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
    values (                                                                   
:new.rowid,SQ_TR_NO.nextval,1,to_char(:old.A),to_char(:new.A),'U',sysdate);    
  end if;                                                                      
  if :new.B = :old.B then                                                      
   null;                                                                       
  else                                                                         
   insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
    values (                                                                   
:new.rowid,SQ_TR_NO.nextval,2,to_char(:old.B),to_char(:new.B),'U',sysdate);    
  end if;                                                                      
  if :new.C = :old.C then                                                      
   null;                                                                       
  else                                                                         
   insert into tab_hist ( r_id,tr_no,column_id,bf_value,af_value,tr_tag,chg_dt)
    values (                                                                   
:new.rowid,SQ_TR_NO.nextval,3,to_char(:old.C),to_char(:new.C),'U',sysdate);    
  end if;                                                                      
 end if;                                                                       
end;                                                                           

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> spool off

근데요.. 이렇게 했더니

5번째 컬럼이 바뀌었는데도

앞의 4번째 값들이 다 나오거든요.

예를들어 5번째 컬럼이 바뀌었으므로

5, 바뀐값, 바뀌기전값

이렇게 나와야 하는데

1,  ,

2,  ,

3,  ,

4, ,

5,바뀐값, 바뀌기전값

이렇게 5개의 row가 히스토리 테이블에 update되요.

아!

맞네요!

제가 null인 경우를 감안 하지 않았네요....

둘다 null인 경우가 있을 수 있는데....

그런 경우는 같다고 봐야죠...

그부분은 한번 직접 고쳐보심이...

원하는 결과치는

if :new.a = :old.a Or :new.a is null and :old.a is null then

이런 식으로 바뀌어야 합니다....