로그 트리거

Log trigger

관계형 데이터베이스에서 로그 트리거 또는 이력 트리거는 데이터베이스 테이블에 행을 삽입 또는 업데이트 또는 삭제하는 변경에 대한 정보를 자동으로 기록하는 메커니즘입니다.

이는 변경 데이터 캡처 및 데이터 웨어하우징에서 서서히 변화하는 치수에 대처하기 위한 특별한 기술입니다.

정의.

감사할 테이블이 있다고 가정합니다.이 표에는 다음 열이 포함되어 있습니다.

Column1, Column2, ..., Columnn

컬럼 Column1는 프라이머리 키로 간주됩니다.

이러한 열은 다음과 같은 유형으로 정의됩니다.

Type1, Type2, ..., Typen

Log 트리거는 다음과 같이 정의된 다른 이력 테이블에 테이블 상의 변경사항(INSERT, UPDATE 및 DELETE 작업)을 기록합니다.

만들다 테이블 이력표 (    열 1   유형 1,    열 2   유형 2,       :        :    열   타이펜,     개시일 날짜,    종료일   날짜 )

위와 같이 이 새 테이블에는 원래 테이블과 동일한 열이 포함되며, 추가로 두 개의 새로운 유형의 열이 포함됩니다.DATETIME:StartDate그리고.EndDate이를 태플버전이라고 합니다.이들 2개의 추가 컬럼은 지정된 엔티티(프라이머리 키의 엔티티)와 관련된 데이터의 유효기간을 정의합니다.즉, 이 컬럼은 데이터 유효기간 동안 데이터가 어떻게 존재했는지를 저장합니다.StartDate(표준) 및EndDate(미포함).

원래의 테이블상의 각 엔티티(구분이 되는 프라이머리 키)에 대해서, 이력 테이블에 다음의 구조가 작성됩니다.데이터를 예로 들 수 있습니다.

example

시간순으로 표시된 경우EndDate 모든 행의 열은 정확히StartDate(있는 경우)의 후계자.정의상 두 행이 공통이라는 것은 아닙니다.EndDate는 포함되지 않습니다.

로그 트리거에는 이전 값(DELETE, UPDATE)과 새 값(INSERT, UPDATE)이 트리거에 노출되는 방법(RDBMS에 따라 다름)에 따라 두 가지 종류가 있습니다.

레코드 데이터 구조 필드로서의 오래된 값과 새로운 값 

만들다 트리거 이력표 온 오리지널 테이블 위해서 삽입, 삭제, 갱신하다 ~하듯이 선언하다 @지금이다 날짜 세트 @지금이다 = 날짜 취득()  /* 섹션 삭제 */  갱신하다 이력표    세트 종료일 = @지금이다  어디에 종료일 IS 특수한 순서    그리고. 열 1 = 늙은.열 1  /* 섹션 삽입 */  삽입 안으로 이력표 (열 1, 열 2, ...,열, 개시일, 종료일)  가치 (신규.열 1, 신규.열 2, ..., 신규.열, @지금이다, 특수한 순서)

가상 테이블 행의 오래된 값과 새로운 값 

만들다 트리거 이력표 온 오리지널 테이블 위해서 삽입, 삭제, 갱신하다 ~하듯이 선언하다 @지금이다 날짜 세트 @지금이다 = 날짜 취득()  /* 섹션 삭제 */  갱신하다 이력표    세트 종료일 = @지금이다   부터 이력표, 삭제했다  어디에 이력표.열 1 = 삭제했다.열 1    그리고. 이력표.종료일 IS 특수한 순서  /* 섹션 삽입 */  삽입 안으로 이력표        (열 1, 열 2, ..., 열, 개시일, 종료일) 선택한다. (열 1, 열 2, ..., 열, @지금이다, 특수한 순서)   부터 삽입된

호환성 노트

  • 함수GetDate()는, 시스템의 날짜와 시각을 취득하기 위해서 사용됩니다.특정 RDBMS 는 다른 함수명을 사용하거나 다른 방법으로 이 정보를 취득할 수 있습니다.
  • 여러 RDBMS(Db2, MySQL)에서는 동일한 트리거를 여러 작업(INSERT, DELETE, UPDATE)에 연결할 수 없습니다.이러한 경우 각 조작에 대해 트리거를 작성해야 합니다.INSERT 조작의 경우 삽입 섹션만 지정해야 하며, DELETE 조작의 경우 삭제 섹션만 지정해야 하며, UPDATE 조작의 경우 위와 같이 두 섹션이 모두 존재해야 합니다(삭제 섹션이 먼저, 삽입 섹션이 먼저 표시됨).UPDATE 연산은 논리적으로 DELETE 연산에 이어 INSERT 연산으로 표현됩니다.
  • 표시된 코드에서는 이전 값과 새 값을 포함하는 레코드 데이터 구조가 호출됩니다.OLD그리고.NEW특정 RDBMS에서는 다른 이름을 사용할 수 있습니다.
  • 표시된 코드에서는 가상 테이블이 호출됩니다.DELETED그리고.INSERTED특정 RDBMS에서는 다른 이름을 사용할 수 있습니다.다른 RDBMS(Db2)에서는 이들 논리 테이블의 이름까지 지정할 수 있습니다.
  • 표시된 코드에서 코멘트는 C/C++ 스타일입니다.특정 RDBMS에서 지원되지 않거나 다른 구문을 사용해야 합니다.
  • 몇 가지 RDBMS에서는 트리거 본체가 다음 사이에 있어야 합니다.BEGIN그리고.END키워드를 지정합니다.

데이터 웨어하우징

천천히 변화하는 치수 관리 방법에 따라 로그 트리거는 다음과 같습니다.

공통 RDB에서의 구현MS

IBM DB2[1]

  • 트리거는 둘 이상의 작업(INSERT, DELETE, UPDATE)에 연결할 수 없으므로 각 작업에 대해 트리거를 생성해야 합니다.
  • 이전 값과 새 값은 레코드 데이터 구조의 필드로 표시됩니다.이러한 레코드의 이름은 다음과 같이 정의할 수 있습니다.O오래된 가치관과N새로운 가치를 위해.
-- INSERT 트리거 만들다 트리거 데이터베이스.표 삽입 끝나고 삽입 온 데이터베이스.오리지널 테이블 참조하고 있다 신규 ~하듯이 N 위해서 각각 배를 젓다 모드 DB2SQL 시작한다.    선언하다 지금이다 타임스탬프;    세트 지금이다 = 현재의 타임스탬프;     삽입 안으로 데이터베이스.이력표 (열 1, 열 2, ..., 열, 개시일, 종료일)    가치 (N.열 1, N.열 2, ..., N.열, 지금이다, 특수한 순서); 끝.;  -- DELETE 트리거 만들다 트리거 데이터베이스.표 삭제 끝나고 삭제 온 데이터베이스.오리지널 테이블 참조하고 있다 늙은 ~하듯이 O 위해서 각각 배를 젓다 모드 DB2SQL 시작한다.    선언하다 지금이다 타임스탬프;    세트 지금이다 = 현재의 타임스탬프;     갱신하다 데이터베이스.이력표       세트 종료일 = 지금이다     어디에 열 1 = O.열 1       그리고. 종료일 IS 특수한 순서; 끝.;  -- UPDATE 트리거 만들다 트리거 데이터베이스.테이블 갱신 끝나고 갱신하다 온 데이터베이스.오리지널 테이블 참조하고 있다 신규 ~하듯이 N 늙은 ~하듯이 O 위해서 각각 배를 젓다 모드 DB2SQL 시작한다.    선언하다 지금이다 타임스탬프;    세트 지금이다 = 현재의 타임스탬프;     갱신하다 데이터베이스.이력표       세트 종료일 = 지금이다     어디에 열 1 = O.열 1       그리고. 종료일 IS 특수한 순서;     삽입 안으로 데이터베이스.이력표 (열 1, 열 2, ..., 열, 개시일, 종료일)    가치 (N.열 1, N.열 2, ..., N.열, 지금이다, 특수한 순서); 끝.;

Microsoft SQL Server[2]

  • 모든 INSERT, DELETE 및 UPDATE 작업에 동일한 트리거를 연결할 수 있습니다.
  • 가상 테이블의 행으로 지정된 이전 값과 새 값DELETED그리고.INSERTED.
만들다 트리거 테이블 트리거 온 오리지널 테이블 위해서 삭제, 삽입, 갱신하다 ~하듯이  선언하다 @지금이다 날짜 세트 @지금이다 = CURRENT_TIMESTamp  갱신하다 이력표    세트 종료일 = @지금이다   부터 이력표, 삭제했다  어디에 이력표.컬럼 아이디 = 삭제했다.컬럼 아이디    그리고. 이력표.종료일 IS 특수한 순서  삽입 안으로 이력표 (컬럼 아이디, 열 2, ..., 열, 개시일, 종료일) 선택한다. 컬럼 아이디, 열 2, ..., 열, @지금이다, 특수한 순서   부터 삽입된

MySQL

  • 트리거는 둘 이상의 작업(INSERT, DELETE, UPDATE)에 연결할 수 없으므로 각 작업에 대해 트리거를 생성해야 합니다.
  • 이전 값과 새 값은 레코드 데이터 구조의 필드로 표시됩니다.Old그리고.New.
구분자 $  /* INSERT에 대한 트리거 */ 만들다 트리거 이력 테이블 삽입 끝나고 삽입 온 오리지널 테이블 위해서 각각 배를 젓다 시작한다.    선언하다 N 날짜;    세트 N = 지금이다();         삽입 안으로 이력표 (열 1, 열 2, ..., 열, 개시일, 종료일)    가치 (신규.열 1, 신규.열 2, ..., 신규.열, N, 특수한 순서); 끝.;  /* DELETE 트리거 */ 만들다 트리거 이력 테이블 삭제 끝나고 삭제 온 오리지널 테이블 위해서 각각 배를 젓다 시작한다.    선언하다 N 날짜;    세트 N = 지금이다();         갱신하다 이력표       세트 종료일 = N     어디에 열 1 = 늙은.열 1       그리고. 종료일 IS 특수한 순서; 끝.;  /* UPDATE 트리거 */ 만들다 트리거 이력 테이블 갱신 끝나고 갱신하다 온 오리지널 테이블 위해서 각각 배를 젓다 시작한다.    선언하다 N 날짜;    세트 N = 지금이다();     갱신하다 이력표       세트 종료일 = N     어디에 열 1 = 늙은.열 1       그리고. 종료일 IS 특수한 순서;     삽입 안으로 이력표 (열 1, 열 2, ..., 열, 개시일, 종료일)    가치 (신규.열 1, 신규.열 2, ..., 신규.열, N, 특수한 순서); 끝.;

오라클

  • 모든 INSERT, DELETE 및 UPDATE 작업에 동일한 트리거를 연결할 수 있습니다.
  • 이전 값과 새 값은 레코드 데이터 구조의 필드로 표시됩니다.:OLD그리고.:NEW.
  • 의 필드의 무효를 테스트할 필요가 있습니다.:NEW프라이머리 키를 정의하는 레코드(DELETE 조작 실행 시)를 사용하여 null 값을 가진 새로운 행이 모든 열에 삽입되지 않도록 합니다.
만들다 또는 교체하다 트리거 테이블 트리거 끝나고 삽입 또는 갱신하다 또는 삭제 온 오리지널 테이블 위해서 각각 배를 젓다 선언하다 지금이다 타임스탬프; 시작한다.    선택한다. CURRENT_TIMESTamp 안으로 지금이다 부터 듀얼;     갱신하다 이력표       세트 종료일 = 지금이다     어디에 종료일 IS 특수한 순서       그리고. 열 1 = :늙은.열 1;     한다면 :신규.열 1 IS 것은 아니다. 특수한 순서 그리고나서       삽입 안으로 이력표 (열 1, 열 2, ..., 열, 개시일, 종료일)        가치 (:신규.열 1, :신규.열 2, ..., :신규.열, 지금이다, 특수한 순서);    끝. 한다면; 끝.;

이력 정보

일반적으로 데이터베이스 백업은 기록 정보를 저장 및 검색하는 데 사용됩니다.데이터베이스 백업은 즉시 사용할 수 있는 기록 정보를 검색하는 효과적인 방법 이상의 보안 메커니즘입니다.

(전체) 데이터베이스 백업은 특정 시점의 데이터 스냅샷일 뿐이므로 각 스냅샷의 정보는 알 수 있지만 그 사이에는 아무것도 알 수 없습니다.데이터베이스 백업의 정보는 시간적으로 구분됩니다.

로그 트리거를 사용하여 알 수 있는 정보는 개별적인 것이 아니라 연속적인 것으로, 임의의 시점에서 정보의 정확한 상태를 알 수 있습니다.단, 이 정보는 에서 제공되는 시간의 정밀도에 한정되어 있습니다.DATETIME사용되는 RDBMS의 데이터 타입.

이점

  • 그것은 간단하다.
  • 상용 제품이 아니라 일반적인 RDBMS에서 사용할 수 있는 기능으로 작동합니다.
  • 일단 생성되면 더 이상 사람의 개입 없이 자동으로 작동합니다.
  • 데이터베이스 테이블 또는 데이터 모델에 대한 충분한 지식이 필요하지 않습니다.
  • 현재 프로그래밍을 변경할 필요가 없습니다.
  • 모든 테이블의 로그 데이터는 다른 테이블에 저장되므로 현재 테이블을 변경할 필요가 없습니다.
  • 이것은 프로그래밍된 문과 애드혹문 모두에 대해 기능합니다.
  • 변경(INSERT, UPDATE 및 DELETE 작업)만 등록되므로 이력 테이블의 증가율은 변경에 비례합니다.
  • 트리거를 데이터베이스의 모든 테이블에 적용할 필요는 없습니다.특정 테이블 또는 테이블의 특정 열에 적용할 수 있습니다.

단점들

  • 변경 내용을 생성하는 사용자(데이터베이스 사용자가 아닌 정보 시스템 사용자)에 대한 정보는 자동으로 저장되지 않습니다.이 정보는 명시적으로 제공될 수 있습니다.정보시스템에서는 실행할 수 있지만 애드혹쿼리에서는 실행할 수 없습니다.

사용 예

테이블의 현재 버전 가져오기

선택한다. 열 1, 열 2, ..., 열   부터 이력표  어디에 종료일 IS 특수한 순서

원래 테이블 전체와 동일한 결과 세트를 반환해야 합니다.

특정 시점의 테이블 버전 가져오기

예를 들어@DATE변수는 관심 지점 또는 시간을 포함합니다. 

선택한다.  열 1, 열 2, ..., 열   부터  이력표  어디에  @날짜. >= 개시일    그리고. (@날짜. < > 종료일 또는 종료일 IS 특수한 순서)

특정 시점의 엔티티 정보 가져오기

예를 들어@DATE변수는 관심 지점 또는 시간을 포함합니다.@KEYvariable에는 대상 엔티티의 프라이머리 키가 포함됩니다. 

선택한다.  열 1, 열 2, ..., 열   부터  이력표  어디에  열 1 = @열쇠    그리고.  @날짜. >= 개시일    그리고. (@날짜. < >  종료일 또는 종료일 IS 특수한 순서)

엔티티 기록 가져오기

예를 들어@KEYvariable에는 대상 엔티티의 프라이머리 키가 포함됩니다. 

선택한다. 열 1, 열 2, ..., 열, 개시일, 종료일   부터 이력표  어디에 열 1 = @열쇠  주문 타고 개시일

엔티티가 생성된 시기와 방법 가져오기

예를 들어@KEYvariable에는 대상 엔티티의 프라이머리 키가 포함됩니다. 

선택한다. H2.열 1, H2.열 2, ..., H2.열, H2.개시일   부터 이력표 ~하듯이 H2 왼쪽 외부 합류하다 이력표 ~하듯이 H1     온 H2.열 1 = H1.열 1    그리고. H2.열 1 = @열쇠    그리고. H2.개시일 = H1.종료일  어디에 H2.종료일 IS 특수한 순서

프라이머리 키의 불변성

트리거에서는 프라이머리 키가 항상 동일해야 하므로 프라이머리 키의 불변성을 확보하거나 최대화하는 것이 바람직합니다.프라이머리 키의 값이 변경되었을 경우 프라이머리 키가 나타내는 엔티티는 자신의 이력을 잃게 됩니다.

주요 키의 불변성을 달성하거나 최대화하기 위한 몇 가지 옵션이 있습니다.

  • 프라이머리 키로서의 대리 키 사용.아이덴티티와 일의성 이외의 의미가 없는 값을 변경할 이유가 없기 때문에 절대 변경되지 않습니다.
  • 프라이머리 키로서 불변의 자연 키를 사용한다.양호한 데이터베이스 설계에서는 변경할 수 있는 자연키를 "실제" 프라이머리 키로 간주해서는 안 됩니다.
  • 변경 가능한 자연키를 프라이머리 키로서 사용하는 것은 권장되지 않습니다.이 키에서는 외부 키가 있는 모든 장소에 변경이 전파됩니다.이 경우 이력표도 영향을 받습니다.

대체 수단

Slowly changing dimension을 방법으로 사용하는 경우가 있습니다.다음 그림은 예를 제시하겠습니다.

Scd 모델

「 」를 참조해 주세요.

메모들

Log 트리거는 Laurence R에 의해 작성되었습니다. Ugalde 트랜잭션 데이터베이스 기록을 자동으로 생성합니다.

레퍼런스

  1. ^ Nareej Sharma et al.의 "데이터베이스 기초" (제1판, Copyright IBM Corpor. 2010)
  2. ^ Thobias Thernström et al.(Microsoft Press, 2009)의 "Microsoft SQL Server 2008 - Database Development"