테이블 내 레코드 변화를 기록하는 방법 (MSSQL CDC)

MS-SQL 2008 버젼부터 CDC(Change Data Capture) 기능이 생겼습니다. 

테이블 내 변경되는 데이터를 기록하는 것으로 (행 전체 또는 일부 컬럼) INSERT, DELETE 는 물론 UPDATE 전/후 모두를 기록할 수 있습니다. 이런 작업은 SQL 트랜잭션 로그에서 정보를 읽어오는 방식이라 OLTP 에는 큰 영향을 미치지 않습니다. 

데이터의 변경을 추적하기 위해서 아주 유용한 서비스이지만 한 가지 제약이 있습니다.

Enterprise 버젼에서만 사용이 가능하다는 점인데요. 

Enterprise 를 사용할 수 없는 환경에서 CDC 와 유사한 운영이 가능한 프로시져를 소개합니다.

아래 프로시져는 tbl_ChangeData 에 현재 상태의 레코드 정보를 기록하는 쿼리입니다.

INSERT/UPDATE/DELETE 가 발생하기 전 이 쿼리를 수행함으로서 현재 상태를 간단히 기록할 수 있습니다.

파라미터

@Table VARCHAR(30)

@Key INT

@CustomerNo INT

@KeyField VARCHAR(30) = 'No_'

쿼리

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

DECLARE @A NVARCHAR(100)

DECLARE @B NVARCHAR(50)

DECLARE @C NVARCHAR(50)

SET @SQL = 'INSERT INTO tbl_ChangeData (CustomerNo,TableName, KeyData, Created, LogText) SELECT ' + STR(@CustomerNo) + ',' + @Table + ''',' + STR(@Key) + ',GETDATE(), '

DECLARE C_DATA CURSOR FOR

SELECT 

COLUMN_NAME, DATA_TYPE, COLLATION_NAME 

FROM 

INFORMATION_SCHEMA.COLUMNS 

WHERE 

TABLE_NAME = @Table

OPEN C_DATA FETCH NEXT FROM C_DATA INTO @A, @B, @C

WHILE (@@fetch_status = 0)

BEGIN

SET @SQL = @SQL + 

CASE

WHEN @B = 'int' OR @B = 'tinyint' OR @B = 'bit' OR @B = 'money' OR @B = 'text' THEN 'CAST(ISNULL([' + @A + '],'''') AS VARCHAR)'

WHEN @B = 'datetime' OR @B = 'smalldatetime' THEN 'CONVERT(VARCHAR, ISNULL([' + @A + '],''''),120)'

WHEN @B = 'varchar' OR @B = 'char' OR @B = 'nvarchar' THEN 'ISNULL([' + @A + '],'''')'

ELSE ' '''',, '

END

IF (@C = 'Korean_Wansung_CS_AS')

SET @SQL = @SQL + ' COLLATE Korean_Wansung_CI_AS'

SET @SQL = @SQL + ' + ''┃'' + '

FETCH NEXT FROM C_DATA INTO @A, @B, @C

END

CLOSE C_DATA

DEALLOCATE C_DATA

SET @SQL = SUBSTRING(@SQL, 0, LEN(@SQL)) + ' FROM ' + @Table + ' (NOLOCK) WHERE ' + @KeyField + ' = ' + STR(@Key)

--print @sql

EXEC sp_executesql @SQL