主页 / user-124809

Steve H.'s questions

Martin Hope
Steve H.
Asked: 2017-05-15 21:59:42 +0800 CST
  • 2

我需要构建一个审计报告来识别两个表之间以及其中一个表中连续行之间的字段更改。第一个表（数据）保存当前数据：

id  data_id field1  field2  data_dttm
1   100     data3   data3   2017-05-03 00:00:00.000

CREATE TABLE [dbo].[data](
[id] [bigint] NULL,
[data_id] [nchar](10) NULL,
[field1] [nchar](10) NULL,
[field2] [nchar](10) NULL,
[adt_dttm] [datetime] NULL
)

Insert INTO data 
Values (1, 100, 'data3', 'data3', '2017-05-03 00:00:00')

第二个表 (data_hst) 在数据发生变化（触发类型操作）之前保存数据。

data_hst_id data_id field1  field2  chng_fld_txt    data_dttm
1           100     data1   data2   field1|field2   2017-05-01 00:00:00.000
2           100     data2   data3   field1          2017-05-02 00:00:00.000

CREATE TABLE [dbo].[data_hst](
[data_hst_id] [bigint] NULL,
[data_id] [bigint] NULL,
[field1] [nvarchar](200) NULL,
[field2] [nvarchar](200) NULL,
[chng_fld_txt] [nvarchar](200) NULL,
[data_dttm] [datetime] NULL
)

Insert INTO data_hst
Values (1, 100, 'data1', 'data2', 'field1|field2', '2017-05-01 00:00:00'),
       (2, 100, 'data2', 'data3', 'field1', '2017-05-02 00:00:00')

“chng_fld_txt”字段包含已修改的字段列表，以竖线分隔。我需要确定数据表行和最近的 data_hst 表行之间发生了什么变化，以及 data_hst 表中连续行之间的变化。跟踪每个更改的审计报告，在它们发生时识别旧值和新值。

结果是这样的：

table_name  db_field    old_value   new_value   data_dttm
data        field1      data1       data2       5/1/2017
data        field2      data2       data3       5/1/2017
data        field1      data2       data3       5/2/2017

我有一些复杂的动态 sql，其中有一个游标适用于第一个条件，但不能同时适用于这两个条件。希望有一种更清洁的方法来满足这两个条件。

    DROP TABLE #changed

    CREATE TABLE #changed(
        [tbl_hst_id] [bigint] NULL,
        [change_field] [nvarchar](200) NULL
    ) ON [PRIMARY]

    DECLARE @db VARCHAR(200)
    SET @db = 'data'
    DECLARE @change_date DATETIME
    DECLARE @change_date_varchar NVARCHAR(200)
    SET @change_date = GETDATE()
    SET @change_date_varchar = LEFT(CONVERT(VARCHAR, @change_date, 120), 10)

    DECLARE @changed_table nvarchar(max)
    SELECT @changed_table = 
    'SELECT TOP 2 t1.' + @db + '_hst_id as tbl_hst_id, t1.change_field
    FROM (
        SELECT A.' + @db + '_hst_id
            ,Split.a.value(''.'', ''VARCHAR(100)'') AS change_field
        FROM (
            SELECT ' + @db + '_hst_id
                ,CAST(''<M>'' + REPLACE(upsrt_chng_fld_txt, '','', ''</M><M>'') + ''</M>'' AS XML) AS String
                ,adt_dttm
            FROM ' + @db + '_hst
            ) AS A
        CROSS APPLY String.nodes(''/M'') AS Split(a)
        WHERE adt_dttm >= DATEADD(D,-4,''' + @change_date_varchar + ''')
        ) T1
    WHERE T1.change_field NOT IN (
            ''upsrt_dttm''
            ,''upsrt_trnsctn_nmbr''
            )'

    exec ('insert #changed ' + @changed_table)

    DECLARE @delta_field VARCHAR(max)
    DECLARE @db_sql_all NVARCHAR(max) = ''

    DECLARE @getDeltaField CURSOR SET @getDeltaField = CURSOR
    FOR
    SELECT change_field
    FROM #changed

    OPEN @getDeltaField

    FETCH NEXT
    FROM @getDeltaField
    INTO @delta_field

    WHILE @@FETCH_STATUS = 0
    BEGIN

        DECLARE @db_sql NVARCHAR(MAX)

        SELECT @db_sql = 'select ''' + @db + ''' as table_name
            , ''1'' as id
            ,''' + @delta_field + ''' as db_field
            ,CAST(hst.' + @delta_field + ' AS NVARCHAR(200)) as old_value
            ,CAST(c.' + @delta_field + ' AS NVARCHAR(200)) as new_value
            --,c.upsrt_usr_id as change_by
            --,c.upsrt_dttm as change_time
            from ' + @db + ' c
            inner join ' + @db + '_hst hst
            on c.' + @db + '_id = hst.' + @db + '_id
            join #changed ch on hst.' + @db + '_hst_id = ch.tbl_hst_id
            UNION '

        SET @db_sql_all = @db_sql_all + @db_sql

        FETCH NEXT
        FROM @getDeltaField
        INTO @delta_field
    END

    CLOSE @getDeltaField

    DEALLOCATE @getDeltaField
    IF len(@db_sql_all) > 0
    BEGIN
        SET @db_sql_all = SUBSTRING(@db_sql_all, 1, len(@db_sql_all) - 6)
    END

    PRINT(@db_sql_all);
    EXEC (@db_sql_all);
sql-server row