복붙노트

[SQL] SQL Server의 역사를 테이블에 역사적 기록을 저장하는 방법

SQL

SQL Server의 역사를 테이블에 역사적 기록을 저장하는 방법

나는 2 개 테이블, 테이블 A와 테이블 A-역사를 가지고있다.

나는 표-A의 내 데이터의 최신 행을하고 싶은, 표-A-역사는 역사적 행을 포함.

나는이 작업을 수행하는 두 가지 방법을 생각할 수 있습니다 :

성능에 관해서 방법 1 또는 2 낫다? 이러한 목표를 달성하기 위해 더 나은 다른 방법이 있나요?

해결법

  1. ==============================

    1.변경 로깅 나는 일반적으로 로그 테이블에 기록 변경에 대한 기본 테이블에 트리거를 사용하여 수행 한 무언가이다. 로그 테이블은 데이터베이스 사용자 행동 및 날짜 / 시간을 기록하기 위해 추가 열이 있습니다.

    변경 로깅 나는 일반적으로 로그 테이블에 기록 변경에 대한 기본 테이블에 트리거를 사용하여 수행 한 무언가이다. 로그 테이블은 데이터베이스 사용자 행동 및 날짜 / 시간을 기록하기 위해 추가 열이 있습니다.

    create trigger Table-A_LogDelete on dbo.Table-A
  for delete
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'delete-deleted', @Now, *
      from deleted
go
exec sp_settriggerorder @triggername = 'Table-A_LogDelete', @order = 'last', @stmttype = 'delete'
go
create trigger Table-A_LogInsert on dbo.Table-A
  for insert
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'insert-inserted', @Now, *
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogInsert', @order = 'last', @stmttype = 'insert'
go
create trigger Table-A_LogUpdate on dbo.Table-A
  for update
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'update-deleted', @Now, *
      from deleted
  insert into Table-A-History
    select SUser_SName(), 'update-inserted', @Now, *
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogUpdate', @order = 'last', @stmttype = 'update'

    로그인 트리거는 항상 마지막에 화재로 설정해야합니다. 그렇지 않으면, 다음 트리거는 원래 트랜잭션을 롤백 할 수 있지만 로그 테이블은 이미 업데이트 된 것입니다. 이 문제의 혼란 상태입니다.

  2. ==============================

    2.기본적으로 당신은 크기에 차 테이블 작은을 유지하면서 테이블 / 감사 변경 사항을 추적하기 위해 찾고 있습니다.

    기본적으로 당신은 크기에 차 테이블 작은을 유지하면서 테이블 / 감사 변경 사항을 추적하기 위해 찾고 있습니다.

    이 문제를 해결하는 방법에는 여러 가지가 있습니다. 단점과 각 방법의 장점은 이하에서 설명된다.

    1 - 트리거와 테이블의 감사.

    당신이있는 표 (삽입, 업데이트, 삭제), 모양을 감사 찾고 있다면 내 원치 않는 거래를하지 못하도록하는 방법 - SQL 토요일 슬라이드 데크 w / 코드 - http://craftydba.com/?page_id=880. 당신이 선택하면 데이터를 XML로 저장되기 때문에 감사 테이블을 채우는 트리거는 여러 테이블에서 정보를 보유 할 수 있습니다. 따라서 XML을 파싱하여 필요한 경우 조치를 해제 삭제할 수 있습니다. 그것은 변경 한 사람과 무엇을 추적합니다.

    선택적으로, 당신은 자신의 파일 그룹에 감사 테이블을 가질 수 있습니다.

    Description:
    Table Triggers For (Insert, Update, Delete)
    Active table has current records.
    Audit (history) table for non-active records.

Pros:
    Active table has smaller # of records.
    Index in active table is small.
    Change is quickly reported in audit table.
    Tells you what change was made (ins, del, upd)

Cons:
    Have to join two tables to do historical reporting.
    Does not track schema changes.

    2 - 효과적인는 기록을 데이트

    당신이 결코 감사 테이블에서 데이터를 제거하려고하지 않으면, 왜 삭제 된 행을 표시하지만 영원히 그것을 유지하지? 사람과 같은 많은 시스템 소프트 레코드가 더 이상 활성 상태 인 경우 표시되지 효과적인 데이트를 사용합니다. 세상은 BI 이것은 타입 2 차원 테이블 (천천히 변화 측정)라고한다. 데이터웨어 하우스 연구소 문서를 참조하십시오. http://www.bidw.org/datawarehousing/scd-type-2/ 각 레코드는 시작 및 종료 날짜가 있습니다.

    모든 활성 기록은 널 (null)의 종료 날짜가 있습니다.

    Description:
    Table Triggers For (Insert, Update, Delete)
    Main table has both active and historical records.

Pros:
    Historical reporting is easy.
    Change is quickly shown in main table.

Cons:
    Main table has a large # of records.
    Index of main table is large.
    Both active & history records in same filegroup.
    Does not tell you what change was made (ins, del, upd)
    Does not track schema changes.

    3 - 변경 데이터 캡처 (엔터프라이즈 기능).

    Micorsoft SQL 서버 2008는 변경 데이터 캡처 기능을 소개했다. 동안 사후 로그 판독기를 사용하여이 데이터를 추적 변화 (CDC), 그것은 어떤 변화를 만들어 같은 것들을 없다. MSDN 세부 - http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

    이 솔루션은 CDC 작업의 실행에 따라 달라집니다. SQL 에이전트에 문제는 데이터의 지연이 나타나지 원인이됩니다.

    변경 데이터 캡처 테이블을 참조하십시오. http://technet.microsoft.com/en-us/library/bb500353(v=sql.105).aspx

    Description:
    Enable change data capture

Pros:
    Do not need to add triggers or tables to capture data.
    Tells you what change was made (ins, del, upd) the _$operation field in 
    <user_defined_table_CT>
    Tracks schema changes.    

Cons:
    Only available in enterprise version.
    Since it reads the log after the fact, time delay in data showing up.
    The CDC tables do not track who or what made the change.
    Disabling CDC removes the tables (not nice)!
    Need to decode and use the _$update_mask to figure out what columns changed.

    4 - 변경 내용 추적 기능 (모든 버전).

    Micorsoft SQL 서버 2008은 변경 내용 추적 기능을 도입했다. CDC는 달리, 모든 버전과 함께 제공; 그러나, 당신이 무슨 일이 있었는지 알아 내기 위해 전화를해야한다는 TSQL 기능의 무리와 함께 제공됩니다.

    그것은 응용 프로그램을 통해 SQL 서버와 동기화 하나의 데이터 소스의 목적을 위해 설계되었다. TechNet의 전체 동기화 프레임 워크가있다.

    http://msdn.microsoft.com/en-us/library/bb933874.aspx http://msdn.microsoft.com/en-us/library/bb933994.aspx http://technet.microsoft.com/en-us/library/bb934145(v=sql.105).aspx

    CDC는 달리, 당신은 제거되기 전에 데이터베이스의 마지막 변경 시간을 지정합니다. 또한, 삽입 및 삭제는하지 데이터를 기록 할. 업데이트는 전용 필드가 변경된 것을 기록합니다.

    다른 대상에 SQL 서버 소스를 동기화되기 때문에,이 잘 작동합니다. 변경 사항을 파악하기 위해주기적인 작업을 작성하지 않는 한 그것은 감사에 대한 좋지 않다.

    당신은 아직도 그 정보 어딘가에 저장해야합니다.

    Description:
    Enable change tracking

Cons:
    Not a good auditing solution

    처음 세 개의 솔루션은 감사를 위해 작동합니다. 내 환경에서 광범위하게 사용 이후 첫 번째 해결책을 좋아한다.

    진정으로

    남자

    프리젠 테이션에서 코드 조각 (자동차 데이터베이스)

    -- 
-- 7 - Auditing data changes (table for DML trigger)
-- 


-- Delete existing table
IF OBJECT_ID('[AUDIT].[LOG_TABLE_CHANGES]') IS NOT NULL 
  DROP TABLE [AUDIT].[LOG_TABLE_CHANGES]
GO


-- Add the table
CREATE TABLE [AUDIT].[LOG_TABLE_CHANGES]
(
  [CHG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
  [CHG_DATE] [datetime] NOT NULL,
  [CHG_TYPE] [varchar](20) NOT NULL,
  [CHG_BY] [nvarchar](256) NOT NULL,
  [APP_NAME] [nvarchar](128) NOT NULL,
  [HOST_NAME] [nvarchar](128) NOT NULL,
  [SCHEMA_NAME] [sysname] NOT NULL,
  [OBJECT_NAME] [sysname] NOT NULL,
  [XML_RECSET] [xml] NULL,
 CONSTRAINT [PK_LTC_CHG_ID] PRIMARY KEY CLUSTERED ([CHG_ID] ASC)
) ON [PRIMARY]
GO

-- Add defaults for key information
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [CHG_DATE];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [CHG_TYPE];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [CHG_BY];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [APP_NAME];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [HOST_NAME];
GO



--
--  8 - Make DML trigger to capture changes
--


-- Delete existing trigger
IF OBJECT_ID('[ACTIVE].[TRG_FLUID_DATA]') IS NOT NULL 
  DROP TRIGGER [ACTIVE].[TRG_FLUID_DATA]
GO

-- Add trigger to log all changes
CREATE TRIGGER [ACTIVE].[TRG_FLUID_DATA] ON [ACTIVE].[CARS_BY_COUNTRY]
  FOR INSERT, UPDATE, DELETE AS
BEGIN

  -- Detect inserts
  IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
  BEGIN
    INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
    SELECT 'INSERT', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)
    RETURN;
  END

  -- Detect deletes
  IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)
  BEGIN
    INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
    SELECT 'DELETE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)
    RETURN;
  END

  -- Update inserts
  IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
  BEGIN
    INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
    SELECT 'UPDATE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)
    RETURN;
  END

END;
GO



--
--  9 - Test DML trigger by updating, deleting and inserting data
--

-- Execute an update
UPDATE [ACTIVE].[CARS_BY_COUNTRY]
SET COUNTRY_NAME = 'Czech Republic'
WHERE COUNTRY_ID = 8
GO

-- Remove all data
DELETE FROM [ACTIVE].[CARS_BY_COUNTRY];
GO

-- Execute the load
EXECUTE [ACTIVE].[USP_LOAD_CARS_BY_COUNTRY];
GO 

-- Show the audit trail
SELECT * FROM [AUDIT].[LOG_TABLE_CHANGES]
GO

-- Disable the trigger
ALTER TABLE [ACTIVE].[CARS_BY_COUNTRY] DISABLE TRIGGER [TRG_FLUID_DATA];

    ** 봐 및 감사 테이블의 느낌 **

  3. ==============================

    3.SQL 서버 (2016+와 푸른)의 최신 버전은 최고 수준의 기능으로 요구 된 정확한 기능을 제공하는 시간 테이블을 가지고있다. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

    SQL 서버 (2016+와 푸른)의 최신 버전은 최고 수준의 기능으로 요구 된 정확한 기능을 제공하는 시간 테이블을 가지고있다. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

    마이크로 소프트의 누군가가 아마도이 페이지를 참조하십시오. :)

  4. ==============================

    4.어떻게 방법 3에 대한 표-A 표-A-역사에 대한보기를 확인합니다. 표-A-역사에 삽입하고 적절한 필터링 로직 표-A를 생성 할 수 있습니다. 그런 식으로 당신은 단지 하나 개의 테이블에 삽입하고 있습니다.

    어떻게 방법 3에 대한 표-A 표-A-역사에 대한보기를 확인합니다. 표-A-역사에 삽입하고 적절한 필터링 로직 표-A를 생성 할 수 있습니다. 그런 식으로 당신은 단지 하나 개의 테이블에 삽입하고 있습니다.

  5. ==============================

    5.그것을 가지고, 더 많은 공간을 소비하더라도뿐만 아니라 가장 최근의 기록을 포함하는 기록 테이블 보고서를 작성 및 변경시 발생 어떻게보고 당신에게 고통을 저장합니다. 내 생각에 대해 생각 뭔가의 가치.

    그것을 가지고, 더 많은 공간을 소비하더라도뿐만 아니라 가장 최근의 기록을 포함하는 기록 테이블 보고서를 작성 및 변경시 발생 어떻게보고 당신에게 고통을 저장합니다. 내 생각에 대해 생각 뭔가의 가치.

    지금까지 성능으로, 나는 그들이 동일 할 것으로 예상한다. 당신이 두 테이블을 마우스 오른쪽 사이에 참조 무결성을 사용하고 있기 때문에, 당신은 확실히 비 HIST 테이블에서 레코드 (옵션 1의 "이동")을 삭제하고 싶지 않아요?

  6. ==============================

    6.옵션 1은 OK입니다. 하지만 당신은 방법 4도 있습니다 :)

    옵션 1은 OK입니다. 하지만 당신은 방법 4도 있습니다 :)

  7. ==============================

    7.나는 방법을 선호하는 것 (1) 또한, 나는 또한이 너무 역사 테이블의 현재 레코드를 유지합니다 그것은 필요에 따라 달라집니다.

    나는 방법을 선호하는 것 (1) 또한, 나는 또한이 너무 역사 테이블의 현재 레코드를 유지합니다 그것은 필요에 따라 달라집니다.

  8. ==============================

    8.에 데이터베이스를 기존의 것은이 쿼리를 실행합니다.

    에 데이터베이스를 기존의 것은이 쿼리를 실행합니다.

    EXECUTE GenerateAudit 'Audit_DB_Name'  , 'dbo', 'Audit_Table_Name' , 0, 1, 1,'',''

    기존 데이터베이스에 삽입, 업데이트 및 데이터의 삭제 기록을 저장해야 새 감사 데이터베이스를 만듭니다.

    CREATE PROC [dbo].[GenerateAudit] @SchemanameAudit SYSNAME = '' --for other database
    ,@Schemaname SYSNAME = 'dbo'
    ,@Tablename SYSNAME
    ,@GenerateScriptOnly BIT = 1
    ,@ForceDropAuditTable BIT = 0
    ,@IgnoreExistingColumnMismatch BIT = 0
    ,@DontAuditforUsers NVARCHAR(4000) = ''
    ,@DontAuditforColumns NVARCHAR(4000) = ''
AS
SET NOCOUNT ON

/*   
Parameters   
@Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'.   
@Tablename            - TableName for which the procs needs to be generated.   
@GenerateScriptOnly - When passed 1 , this will generate the scripts alone..   
                      When passed 0 , this will create the audit tables and triggers in the current database.   
                      Default value is 1   
@ForceDropAuditTable - When passed 1 , will drop the audit table and recreate 
                       When passed 0 , will generate the alter scripts 
                       Default value is 0 
@IgnoreExistingColumnMismatch - When passed 1 , will not stop with the error on the mismatch of existing column and will create the trigger. 
                                When passed 0 , will stop with the error on the mismatch of existing column. 
                                Default value is 0 
@DontAuditforUsers - Pass the UserName as comma seperated for whom the audit is not required.
                     Default value is '' which will do audit for all the users.

@DontAuditforColumns - Pass the ColumnNames as comma seperated for which the audit is not required.
      Default value is '' which will do audit for all the users.
*/
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQLTrigger VARCHAR(MAX)
DECLARE @ErrMsg VARCHAR(MAX)
DECLARE @AuditTableName SYSNAME
DECLARE @QuotedSchemaName SYSNAME
DECLARE @QuotedSchemaNameAudit SYSNAME --for other database
DECLARE @QuotedTableName SYSNAME
DECLARE @QuotedAuditTableName SYSNAME
DECLARE @InsertTriggerName SYSNAME
DECLARE @UpdateTriggerName SYSNAME
DECLARE @DeleteTriggerName SYSNAME
DECLARE @QuotedInsertTriggerName SYSNAME
DECLARE @QuotedUpdateTriggerName SYSNAME
DECLARE @QuotedDeleteTriggerName SYSNAME
DECLARE @DontAuditforUsersTmp NVARCHAR(4000)

SELECT @AuditTableName = @Tablename + '_Audit'

SELECT @QuotedSchemaNameAudit = QUOTENAME(@SchemanameAudit) --for other database

SELECT @QuotedSchemaName = QUOTENAME(@Schemaname)

SELECT @QuotedTableName = QUOTENAME(@Tablename)

SELECT @QuotedAuditTableName = QUOTENAME(@AuditTableName)

SELECT @InsertTriggerName = @Tablename + '_Insert'

SELECT @UpdateTriggerName = @Tablename + '_Update'

SELECT @DeleteTriggerName = @Tablename + '_Delete'

SELECT @QuotedInsertTriggerName = QUOTENAME(@InsertTriggerName)

SELECT @QuotedUpdateTriggerName = QUOTENAME(@UpdateTriggerName)

SELECT @QuotedDeleteTriggerName = QUOTENAME(@DeleteTriggerName)

IF LTRIM(RTRIM(@DontAuditforUsers)) <> ''
BEGIN
    IF RIGHT(@DontAuditforUsers, 1) = ','
    BEGIN
        SELECT @DontAuditforUsersTmp = LEFT(@DontAuditforUsers, LEN(@DontAuditforUsers) - 1)
    END
    ELSE
    BEGIN
        SELECT @DontAuditforUsersTmp = @DontAuditforUsers
    END

    SELECT @DontAuditforUsersTmp = REPLACE(@DontAuditforUsersTmp, ',', ''',''')
END

SELECT @DontAuditforColumns = ',' + UPPER(@DontAuditforColumns) + ','

IF NOT EXISTS (
        SELECT 1
        FROM sys.objects
        WHERE Name = @TableName
            AND Schema_id = Schema_id(@Schemaname)
            AND Type = 'U'
        )
BEGIN
    SELECT @ErrMsg = @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedTableName + ' Table Not Found '

    RAISERROR (
            @ErrMsg
            ,16
            ,1
            )

    RETURN
END

----------------------------------------------------------------------------------------------------------------------   
-- Audit Create OR Alter table    
----------------------------------------------------------------------------------------------------------------------   
DECLARE @ColList VARCHAR(MAX)
DECLARE @InsertColList VARCHAR(MAX)
DECLARE @UpdateCheck VARCHAR(MAX)
DECLARE @NewAddedCols TABLE (
    ColumnName SYSNAME
    ,DataType SYSNAME
    ,CharLength INT
    ,Collation SYSNAME NULL
    ,ChangeType VARCHAR(20) NULL
    ,MainTableColumnName SYSNAME NULL
    ,MainTableDataType SYSNAME NULL
    ,MainTableCharLength INT NULL
    ,MainTableCollation SYSNAME NULL
    ,AuditTableColumnName SYSNAME NULL
    ,AuditTableDataType SYSNAME NULL
    ,AuditTableCharLength INT NULL
    ,AuditTableCollation SYSNAME NULL
    )

SELECT @ColList = ''

SELECT @UpdateCheck = ' '

SELECT @SQL = ''

SELECT @InsertColList = ''

SELECT @ColList = @ColList + CASE SC.is_identity
        WHEN 1
            THEN 'CONVERT(' + ST.name + ',' + QUOTENAME(SC.name) + ') as ' + QUOTENAME(SC.name)
        ELSE QUOTENAME(SC.name)
        END + ','
    ,@InsertColList = @InsertColList + QUOTENAME(SC.name) + ','
    ,@UpdateCheck = @UpdateCheck + CASE 
        WHEN CHARINDEX(',' + UPPER(SC.NAME) + ',', @DontAuditforColumns) = 0
            THEN 'CASE WHEN UPDATE(' + QUOTENAME(SC.name) + ') THEN ''' + QUOTENAME(SC.name) + '-'' ELSE '''' END + ' + CHAR(10)
        ELSE ''
        END
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
    AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
    AND SO.name = @Tablename
    AND UPPER(ST.name) <> UPPER('timestamp')

SELECT @ColList = SUBSTRING(@ColList, 1, LEN(@ColList) - 1)

SELECT @UpdateCheck = SUBSTRING(@UpdateCheck, 1, LEN(@UpdateCheck) - 3)

SELECT @InsertColList = SUBSTRING(@InsertColList, 1, LEN(@InsertColList) - 1)

SELECT @InsertColList = @InsertColList + ',AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns'

IF EXISTS (
        SELECT 1
        FROM sys.objects
        WHERE Name = @AuditTableName
            AND Schema_id = Schema_id(@Schemaname)
            AND Type = 'U'
        )
    AND @ForceDropAuditTable = 0
BEGIN
    ----------------------------------------------------------------------------------------------------------------------   
    -- Get the comparision metadata for Main and Audit Tables 
    ----------------------------------------------------------------------------------------------------------------------   
    INSERT INTO @NewAddedCols (
        ColumnName
        ,DataType
        ,CharLength
        ,Collation
        ,ChangeType
        ,MainTableColumnName
        ,MainTableDataType
        ,MainTableCharLength
        ,MainTableCollation
        ,AuditTableColumnName
        ,AuditTableDataType
        ,AuditTableCharLength
        ,AuditTableCollation
        )
    SELECT ISNULL(MainTable.ColumnName, AuditTable.ColumnName)
        ,ISNULL(MainTable.DataType, AuditTable.DataType)
        ,ISNULL(MainTable.CharLength, AuditTable.CharLength)
        ,ISNULL(MainTable.Collation, AuditTable.Collation)
        ,CASE 
            WHEN MainTable.ColumnName IS NULL
                THEN 'Deleted'
            WHEN AuditTable.ColumnName IS NULL
                THEN 'Added'
            ELSE NULL
            END
        ,MainTable.ColumnName
        ,MainTable.DataType
        ,MainTable.CharLength
        ,MainTable.Collation
        ,AuditTable.ColumnName
        ,AuditTable.DataType
        ,AuditTable.CharLength
        ,AuditTable.Collation
    FROM (
        SELECT SC.Name AS ColumnName
            ,ST.Name AS DataType
            ,SC.is_identity AS isIdentity
            ,SC.Max_length AS CharLength
            ,SC.Collation_Name AS Collation
        FROM SYS.COLUMNS SC
        JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
        JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
        JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
            AND ST.system_type_id = SC.system_type_id
        WHERE SCH.Name = @Schemaname
            AND SO.name = @Tablename
            AND UPPER(ST.name) <> UPPER('timestamp')
        ) MainTable
    FULL OUTER JOIN (
        SELECT SC.Name AS ColumnName
            ,ST.Name AS DataType
            ,SC.is_identity AS isIdentity
            ,SC.Max_length AS CharLength
            ,SC.Collation_Name AS Collation
        FROM SYS.COLUMNS SC
        JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
        JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
        JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
            AND ST.system_type_id = SC.system_type_id
        WHERE SCH.Name = @Schemaname
            AND SO.name = @AuditTableName
            AND UPPER(ST.name) <> UPPER('timestamp')
            AND SC.Name NOT IN (
                'AuditDataState'
                ,'AuditDMLAction'
                ,'AuditUser'
                ,'AuditDateTime'
                ,'UpdateColumns'
                )
        ) AuditTable ON MainTable.ColumnName = AuditTable.ColumnName

    ----------------------------------------------------------------------------------------------------------------------   
    -- Find data type changes between table 
    ----------------------------------------------------------------------------------------------------------------------   
    IF EXISTS (
            SELECT *
            FROM @NewAddedCols NC
            WHERE NC.MainTableColumnName = NC.AuditTableColumnName
                AND (
                    NC.MainTableDataType <> NC.AuditTableDataType
                    OR NC.MainTableCharLength > NC.AuditTableCharLength
                    OR NC.MainTableCollation <> NC.AuditTableCollation
                    )
            )
    BEGIN
        SELECT CONVERT(VARCHAR(50), CASE 
                    WHEN NC.MainTableDataType <> NC.AuditTableDataType
                        THEN 'DataType Mismatch'
                    WHEN NC.MainTableCharLength > NC.AuditTableCharLength
                        THEN 'Length in maintable is greater than Audit Table'
                    WHEN NC.MainTableCollation <> NC.AuditTableCollation
                        THEN 'Collation Difference'
                    END) AS Mismatch
            ,NC.MainTableColumnName
            ,NC.MainTableDataType
            ,NC.MainTableCharLength
            ,NC.MainTableCollation
            ,NC.AuditTableColumnName
            ,NC.AuditTableDataType
            ,NC.AuditTableCharLength
            ,NC.AuditTableCollation
        FROM @NewAddedCols NC
        WHERE NC.MainTableColumnName = NC.AuditTableColumnName
            AND (
                NC.MainTableDataType <> NC.AuditTableDataType
                OR NC.MainTableCharLength > NC.AuditTableCharLength
                OR NC.MainTableCollation <> NC.AuditTableCollation
                )

        RAISERROR (
                'There are differences in Datatype or Lesser Length or Collation difference between the Main table and Audit Table. Please refer the output'
                ,16
                ,1
                )

        IF @IgnoreExistingColumnMismatch = 0
        BEGIN
            RETURN
        END
    END

    ----------------------------------------------------------------------------------------------------------------------   
    -- Find the new and deleted columns  
    ----------------------------------------------------------------------------------------------------------------------   
    IF EXISTS (
            SELECT *
            FROM @NewAddedCols
            WHERE ChangeType IS NOT NULL
            )
    BEGIN
        SELECT @SQL = @SQL + 'ALTER TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CASE 
                WHEN NC.ChangeType = 'Added'
                    THEN ' ADD ' + QUOTENAME(NC.ColumnName) + ' ' + NC.DataType + ' ' + CASE 
                            WHEN NC.DataType IN (
                                    'char'
                                    ,'varchar'
                                    ,'nchar'
                                    ,'nvarchar'
                                    )
                                AND NC.CharLength = - 1
                                THEN '(max) COLLATE ' + NC.Collation + ' NULL '
                            WHEN NC.DataType IN (
                                    'char'
                                    ,'varchar'
                                    )
                                THEN '(' + CONVERT(VARCHAR(5), NC.CharLength) + ') COLLATE ' + NC.Collation + ' NULL '
                            WHEN NC.DataType IN (
                                    'nchar'
                                    ,'nvarchar'
                                    )
                                THEN '(' + CONVERT(VARCHAR(5), NC.CharLength / 2) + ') COLLATE ' + NC.Collation + ' NULL '
                            ELSE ''
                            END
                WHEN NC.ChangeType = 'Deleted'
                    THEN ' DROP COLUMN ' + QUOTENAME(NC.ColumnName)
                END + CHAR(10)
        FROM @NewAddedCols NC
        WHERE NC.ChangeType IS NOT NULL
    END
END
ELSE
BEGIN
    SELECT @SQL = '  IF EXISTS (SELECT 1    
                                          FROM sys.objects    
                                         WHERE Name=''' + @AuditTableName + '''   
                                           AND Schema_id=Schema_id(''' + @Schemaname + ''')   
                                           AND Type = ''U'')   
                            DROP TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '

                    SELECT ' + @ColList + '   
                        ,AuditDataState=CONVERT(VARCHAR(10),'''')    
                        ,AuditDMLAction=CONVERT(VARCHAR(10),'''')     
                        ,AuditUser =CONVERT(SYSNAME,'''')   
                        ,AuditDateTime=CONVERT(DATETIME,''01-JAN-1900'')   
                        ,UpdateColumns = CONVERT(VARCHAR(MAX),'''')  
                        Into ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '   
                    FROM ' + @QuotedSchemaName + '.' + @QuotedTableName + '   
                    WHERE 1=2 
                    ALTER TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + 
        '.' + @QuotedAuditTableName + ' ADD AuditId INT IDENTITY(1,1)
                    '
        --imran tag added the above alter table auto identity         
END

IF @GenerateScriptOnly = 1
BEGIN
    PRINT REPLICATE('-', 200)
    PRINT '--Create \ Alter Script Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName
    PRINT REPLICATE('-', 200)
    PRINT @SQL

    IF LTRIM(RTRIM(@SQL)) <> ''
    BEGIN
        PRINT 'GO'
    END
    ELSE
    BEGIN
        PRINT '-- No changes in table structure'
    END
END
ELSE
BEGIN
    IF RTRIM(LTRIM(@SQL)) = ''
    BEGIN
        PRINT 'No Table Changes Found'
    END
    ELSE
    BEGIN
        PRINT 'Creating \ Altered Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName

        EXEC (@SQL)

        PRINT 'Audit table ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + ' Created \ Altered succesfully'
    END
END

----------------------------------------------------------------------------------------------------------------------   
-- Create Insert Trigger   
----------------------------------------------------------------------------------------------------------------------   
SELECT @SQL = '   
IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Insert' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName

SELECT @SQLTrigger = '   
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + '
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR INSERT   
AS   
'

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'

    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''New'',''Insert'',SUSER_SNAME(),getdate(),''''  FROM INSERTED '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END

IF @GenerateScriptOnly = 1
BEGIN
    PRINT REPLICATE('-', 200)
    PRINT '--Create Script Insert Trigger for ' + @QuotedSchemaName + '.' + @QuotedTablename
    PRINT REPLICATE('-', 200)
    PRINT @SQL
    PRINT 'GO'
    PRINT @SQLTrigger
    PRINT 'GO'
END
ELSE
BEGIN
    PRINT 'Creating Insert Trigger ' + @QuotedInsertTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTablename

    EXEC (@SQL)

    EXEC (@SQLTrigger)

    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + ' Created succesfully'
END

----------------------------------------------------------------------------------------------------------------------   
-- Create Delete Trigger   
----------------------------------------------------------------------------------------------------------------------   
SELECT @SQL = '   

IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Delete' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + + @QuotedDeleteTriggerName + '   
'

SELECT @SQLTrigger = '   
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + '   
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR DELETE   
AS   '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'

    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '  INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''Old'',''Delete'',SUSER_SNAME(),getdate(),''''  FROM DELETED'

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END

IF @GenerateScriptOnly = 1
BEGIN
    PRINT REPLICATE('-', 200)
    PRINT '--Create Script Delete Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
    PRINT REPLICATE('-', 200)
    PRINT @SQL
    PRINT 'GO'
    PRINT @SQLTrigger
    PRINT 'GO'
END
ELSE
BEGIN
    PRINT 'Creating Delete Trigger ' + @QuotedDeleteTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTableName

    EXEC (@SQL)

    EXEC (@SQLTrigger)

    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + ' Created succesfully'
END

----------------------------------------------------------------------------------------------------------------------   
-- Create Update Trigger   
----------------------------------------------------------------------------------------------------------------------   
SELECT @SQL = '   

IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Update' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '   
'

SELECT @SQLTrigger = '   
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '     
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR UPDATE   
AS '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'

    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '  

    DECLARE @UpdatedCols varchar(max)

   SELECT @UpdatedCols = ' + @UpdateCheck + '

   IF LTRIM(RTRIM(@UpdatedCols)) <> ''''
   BEGIN
          INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''New'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols  FROM INSERTED    

          INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''Old'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols  FROM DELETED 
   END'

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
    SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END

IF @GenerateScriptOnly = 1
BEGIN
    PRINT REPLICATE('-', 200)
    PRINT '--Create Script Update Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
    PRINT REPLICATE('-', 200)
    PRINT @SQL
    PRINT 'GO'
    PRINT @SQLTrigger
    PRINT 'GO'
END
ELSE
BEGIN
    PRINT 'Creating Delete Trigger ' + @QuotedUpdateTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTableName

    EXEC (@SQL)

    EXEC (@SQLTrigger)

    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '  Created succesfully'
END

SET NOCOUNT OFF

    9. from https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 결과 케이스를 구분 정렬 문으로 SQL 주문을 사용 하는가?  (0) 2020.04.29
[SQL] org.postgresql.util.PSQLException : FATAL : 이미 미안 해요, 너무 많은 클라이언트  (0) 2020.04.29
[SQL] SQL Server 2008의 매개 변수보기 만들기  (0) 2020.04.29
[SQL] MySQL은 매장 IP 주소에 가장 효율적인 방법 [중복]  (0) 2020.04.29
[SQL] Laravel 웅변 대 쿼리 빌더 - 사용 설득력이 성능을 저하하는 이유 [폐쇄]  (0) 2020.04.29
,

티스토리툴바