Замена Change Data Capture

@new_in_net · Регистрация: 11.11.2009

Студворк — интернет-сервис помощи студентам

У меня стоит MS SQL Server 2008 Standard Edition.
Мне нужно для нескольких таблиц создать data change log - записывать кто, когда и что изменил.
Есть в MS SQL Server 2008 такая фича как Change Data Capture. Вроде как понравилась, но проблема в том что она показывает только последнее изменение (чтоб показывало всю историю вроде как нужен Enterprise Edition), плюс мне надо знать какой юзверь это изменение сделал...

Думаю что такое делалось и не раз.

В итоге думаю, буду делать сам на тригерах. Вопрос такой, так как отслеживать нужно несколько таблиц (может все) хорошо бы создать одну таблицу куда можно было бы сбрасывать изменения для всех таблиц со структурой типа:
(TableID, RecordID, ChangeDateTime, UserID, OldValues, NewValues)

и на триггере записывать каждую запись из inserted/deleted в эту таблицу.

Вопрос - Чтоб не писать тригер для каждой таблицы индивидуально, существует ли хороший/удобный способ собрать (конкатенировать) значения всех полей (скажем в формате CSV) в одну переменную чтоб записать в OldValues или в NewValues?

Ссылки на похожие решения?

Добавлено через 3 часа 41 минуту
Но это не то что хотел, но нашел неплохое решение, чуть подправить и вперед:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
CREATE TRIGGER TRG_##YOUR_TABLE##
 
ON [DBO].[##YOUR_TABLE##]
 
FOR DELETE,INSERT,UPDATE
 
AS
 
-- JUST CHANGE ##YOUR_TABLE## INTO YOUR OWN TABLENAME TO MAKE IT WORK 
 
DECLARE @ACT CHAR(6)
 
DECLARE @DEL BIT
 
DECLARE @INS BIT 
 
DECLARE @SQLSTRING VARCHAR(2000)
 
 
 
SET @DEL = 0
 
SET @INS = 0
 
 
 
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
 
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 
 
 
 
IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
 
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
 
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'
 
 
 
IF @INS = 0 AND @DEL = 0 RETURN
 
 
 
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_##YOUR_TABLE##]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
 
BEGIN
 
    -- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
 
    DECLARE @MEMTABLE TABLE
 
    ( 
 
        ID INT IDENTITY
 
        ,COLUMNAME SYSNAME
 
        ,TYPENAME VARCHAR(20)
 
     )
 
    -- INSERT THE COLUMNAMES AND THE DATATYPES
 
    INSERT @MEMTABLE 
 
        (COLUMNAME,TYPENAME) 
 
        SELECT NAME,TYPE_NAME(XTYPE) 
 
        FROM SYSCOLUMNS 
 
        WHERE ID = OBJECT_ID('[DBO].[##YOUR_TABLE##]') 
 
        ORDER BY COLID
 
 
 
    DECLARE @CUR INTEGER
 
    DECLARE @MAX INTEGER
 
    DECLARE @SQLSTR AS VARCHAR(8000)
 
    DECLARE @CURCOL SYSNAME
 
    DECLARE @COLTYPE AS VARCHAR(10)
 
 
 
    -- SETUP VARIABLES
 
    SET @SQLSTR = ''
 
    SET @CUR=1
 
    SELECT @MAX = MAX(ID) FROM @MEMTABLE
 
 
 
    -- LOOP EVEY FIELD
 
    WHILE @CUR <= @MAX
 
    BEGIN
 
        -- GET VALUES FROM THE MEMTABLE    
 
        SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
 
        IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
 
            -- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN 
 
            -- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES 
 
            SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '
 
        ELSE
 
            -- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
 
            SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
 
        IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
 
        SET @CUR = @CUR + 1
 
    END
 
    -- ADD THE AUDIT FIELDS
 
    SET @SQLSTR = @SQLSTR +',CAST(''      '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE'
 
    -- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
 
    SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_##YOUR_TABLE##] FROM [DBO].[##YOUR_TABLE##]'
 
    EXEC(@SQLSTR)
 
END
 
 
 
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
 
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'DELETE' ,GETDATE() FROM DELETED
 
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT [DBO].[AUDIT_##YOUR_TABLE##] 
 
SELECT DELETED.*,GETDATE()
 
FROM INSERTED
 
INNER JOIN DELETED ON DELETED.StatusID = INSERTED.StatusID 
 
WHERE (INSERTED.Status <> DELETED.STATUS) 
 
OR (INSERTED.CountAlerts <> DELETED.CountAlerts) 
 
OR (INSERTED.AlertViewed <> DELETED.AlertViewed)

@new_in_net · 29.07.2010, 23:21 **[ТС]**

Нашел способ сделать так как я хочу - с помощю XML преобразований.
Вот как записать все столбцы одной записи таблицы в одно поле и прочитать обратно как строку

T-SQL
1
2
3
4
5
6
7
8
9
--writing
DECLARE @docHandle int, @xmlDocument NVARCHAR(MAX)
SET @xmlDocument = (SELECT TOP 1 * FROM Contact FOR XML AUTO)
PRINT @xmlDocument
 
--parsing back
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
SELECT * FROM OPENXML(@docHandle, N'/Contact') WITH Contact
EXEC sp_xml_removedocument @docHandle

А вот так записать все столбцы нескольких записей таблицы в одно поле и прочитать обратно как строки:

T-SQL
1
2
3
4
5
6
7
8
9
--writing
DECLARE @docHandle int, @xmlDocument NVARCHAR(MAX)
SET @xmlDocument = (SELECT top 10 * FROM Contact FOR XML AUTO, ROOT('Contacts'))
PRINT @xmlDocument
 
--parsing back
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
SELECT * FROM OPENXML(@docHandle, N'/Contacts/Contact') WITH Contact
EXEC sp_xml_removedocument @docHandle

остается дело за маленьким - в триггере каждую строку таблицу inserted конвертить в строку (1 вариант) и записывать в таблицу со структурой я указал в самом начале.
Закончу - поделюсь что получилось...

@Страдалецъ · 29.07.2010, 23:37

Это все конечно здорово и нечто подобное я тоже делал, но я не смог найти приемлимого способа логировать блобы.

@new_in_net · 30.07.2010, 00:09 **[ТС]**

а вот таблица:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    CREATE TABLE [dbo].[tb_secDataTrail](
        ID INT IDENTITY(1, 1) NOT NULL,
        TableName INT NOT NULL,
        DateTimeStamp DATETIME NOT NULL,
        HostName VARCHAR(100) NOT NULL,
        UserName VARCHAR(100) NOT NULL,
        Op CHAR(1) NOT NULL,
        OldValues XML,
        NewValues XML,
     CONSTRAINT [PK_tb_secDataTrail] PRIMARY KEY CLUSTERED
     (
        ID
     )
    ) ON [PRIMARY]
 
    CREATE NONCLUSTERED INDEX IX_tb_secDataTrail_ByTable ON dbo.tb_secDataTrail
        (
        TableName,
        ChangeDateTimeStamp,
        Op
        )

Поле XML по документации может хранить данные до 2 ГБ и по идее блобы тоже могут туда быть записаны как CDATA
Не проверял, так как в данный момент BLOB-ов нет которых надо отслеживать.

Можно тип XML заменить на VARCHAR(MAX) но вряд ли это улучшит ситуацию

я бы блобы игнорировал бы...

@new_in_net · 05.08.2010, 19:08 **[ТС]**

Вот решил поделиться с тем, что у меня получилось.
На мой взгляд получилось довольно элегантно:

одна таблица, в которой хранятся все изменения
одна SP которая добавляет-удаляет триггер отслеживания изменений в любую таблицу
и еще одна SP которая возвращает любое изменение в виде таблицы.

Итак, собственно SQL скрипт для создания всех указанных выше объектов

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
IF NOT Exists( SELECT * FROM sys.objects WHERE name = 'tb_secDataTrail' )
BEGIN
-- drop table tb_secDataTrail
    CREATE TABLE [dbo].[tb_secDataTrail](
        ID INT IDENTITY(1, 1) NOT NULL,
        TableName VARCHAR(100) NOT NULL,
        DateTimeStamp DATETIME NOT NULL,
        HostName VARCHAR(100) NOT NULL,
        UserName VARCHAR(100) NOT NULL,
        UserLogin VARCHAR(100) NOT NULL,
        Op CHAR(1) NOT NULL,
        FieldValues XML
     CONSTRAINT [PK_tb_secDataTrail] PRIMARY KEY CLUSTERED
     (
        ID
     )
    ) ON [PRIMARY]
 
    CREATE NONCLUSTERED INDEX IX_tb_secDataTrail_ByTable ON dbo.tb_secDataTrail
        (
        TableName,
        DateTimeStamp,
        Op
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    PRINT 'Table tb_secDataTrail has been successfuly created';
END;
 
------------------------------------------------------------------------------------------
IF Exists( SELECT * FROM sys.objects WHERE name = 'sp_secDataTrailAddTable' )
    DROP PROCEDURE dbo.sp_secDataTrailAddTable
GO
CREATE PROC [dbo].sp_secDataTrailAddTable(
    @TableName VARCHAR(100),
    @CreateTrigger bit = 1
)
AS
BEGIN 
    DECLARE @AutoIDField VARCHAR(100);
    SELECT @AutoIDField = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 AND TABLE_NAME = @TableName
 
 
    DECLARE @SQL_DROP VARCHAR(MAX) = '
        IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''#TableName#_DataTrailing''))
            DROP TRIGGER dbo.#TableName#_DataTrailing';
    
    DECLARE @SQL_CREATE VARCHAR(MAX)
    SET @SQL_CREATE = '
CREATE TRIGGER dbo.#TableName#_DataTrailing ON dbo.#TableName#
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON
    SELECT ''D'' as Op, * INTO #Temp#TableName# FROM Deleted
    UNION ALL
    SELECT ''I'', * FROM Inserted
    '
    IF @AutoIDField IS NULL
        SET @SQL_CREATE = @SQL_CREATE +
    'ALTER TABLE #Temp#TableName# ADD
        AutoID INT IDENTITY(1, 1) NOT NULL;
    '
SET @SQL_CREATE = @SQL_CREATE +
    'DECLARE @Date DATETIME = GETDATE();
    INSERT INTO tb_secDataTrail
        (TableName, DateTimeStamp, HostName, UserName, UserLogin, Op, FieldValues)
        SELECT ''#TableName#'', @Date, HOST_NAME(), SYSTEM_USER, dbo.fn_secGetCurrUserFullName(), Op, 
            (SELECT * FROM #Temp#TableName# Rec WHERE Rec.#AutoIDField# = Main.#AutoIDField# AND Rec.Op = Main.Op FOR XML AUTO)
            FROM #Temp#TableName# Main
    DROP TABLE #Temp#TableName#
END;';
    
    SET @SQL_DROP = REPLACE(@SQL_DROP, '#TableName#', @TableName);
    SET @SQL_CREATE = REPLACE(@SQL_CREATE, '#TableName#', @TableName);
    SET @SQL_CREATE = REPLACE(@SQL_CREATE, '#AutoIDField#', IsNull(@AutoIDField, 'AutoID'));
    
    --PRINT @SQL_DROP
    EXEC(@SQL_DROP)
    
    if @CreateTrigger <> 1
        PRINT @SQL_CREATE
    ELSE
        EXEC(@SQL_CREATE)
END;
GO
 
IF Exists( SELECT * FROM sys.objects WHERE name = 'sp_secDataTrailGetDataSet' )
    DROP PROCEDURE dbo.sp_secDataTrailGetDataSet
GO
CREATE PROC [dbo].sp_secDataTrailGetDataSet(
    @ID INT
)
AS
BEGIN 
    DECLARE @TableName VARCHAR(100), @SQL VARCHAR(MAX)
    SELECT @TableName = TableName FROM tb_secDataTrail WHERE ID = @ID
    --PRINT @TableName
    SET @SQL = '
    DECLARE @xmlDocument VARCHAR(MAX), @docHandle INT
    SELECT @xmlDocument = CAST(FieldValues as VARCHAR(MAX)) FROM tb_secDataTrail WHERE ID = '+Cast(@ID as VARCHAR(100))+'
    --PRINT @xmlDocument
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
    SELECT * FROM OPENXML(@docHandle, N''/Rec'') WITH '+@TableName+'
    EXEC sp_xml_removedocument @docHandle '
    --PRINT (@SQL)
    EXEC (@SQL)
END;
GO

Вот так включаем систему отслеживалиния для любой таблицы:

T-SQL
1
EXEC sp_secDataTrailAddTable @TableName = 'Customers'

так ее выключаем:

T-SQL
1
EXEC sp_secDataTrailAddTable @TableName = 'Customers', @CreateTrigger = 0;

так делаем поиск по любому нам известному полю:

T-SQL
1
2
3
4
5
6
SELECT * FROM tb_secDataTrail
WHERE TableName = 'Customers' AND FieldValues.value('(/Rec/@ID)[1]', 'int') = 100
 
-- или так
SELECT * FROM tb_secDataTrail
WHERE TableName = 'Customers' AND FieldValues.value('(/Rec/@Name)[1]', 'varchar(50)') = 'MyCustomerName'

а так найденную нами строку с изменениями (скажем ID = 10) мы конвертируем XML в табличную форму:

T-SQL
1
EXEC sp_secDataTrailGetDataSet 10

Система получилась универсальной и все работает довольно быстро.

Скорость для моих целей меня вполне устраивает, но если понадобится, в будущем для увеличения скорости можно было бы в таблицу изменений tb_secDataTrail добавить поле

T-SQL
1
KeyFields VARCHAR(100)

и в триггере довавлять туда все ключевые поля через запятую или еще какой символ.
Тогда индекс нужно будет подправить как:

T-SQL
1
2
3
4
5
6
7
CREATE NONCLUSTERED INDEX IX_tb_secDataTrail_ByTable ON dbo.tb_secDataTrail
    (
    TableName,
    KeyFields,
    DateTimeStamp,
    Op
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

и соответственно поиск по таблице+PK будет моментальным.

Вышеописанное мною найденное решение - абсолютно готовое универсальное решение для любых таблиц и систем - используйте

@Alesandr · 05.12.2012, 18:56

Здравствуйте. Спасибо автору, хороший пример. А какие манипуляции надо сделать чтобы не потерять при преобразовании в xml текстовую информацию содержащую знаки пробел %, \ /

Новые блоги и статьи Все статьи Все блоги /
Изучаю kubernetes lagorue 13.01.2026 А пригодятся-ли мне знания kubernetes в России?	Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .
Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .	Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .

@Страдалецъ Тимуровец 445 / 285 / 50 Регистрация: 10.09.2009 Сообщений: 963
	29.07.2010, 23:37
	Это все конечно здорово и нечто подобное я тоже делал, но я не смог найти приемлимого способа логировать блобы. 0

@Alesandr 0 / 0 / 0 Регистрация: 11.06.2014 Сообщений: 8
	05.12.2012, 18:56
	Здравствуйте. Спасибо автору, хороший пример. А какие манипуляции надо сделать чтобы не потерять при преобразовании в xml текстовую информацию содержащую знаки пробел %, \ / 0