Создание триггеров аудита в SQL Server

Question

Создание триггеров аудита в SQL Server

Мне нужно реализовать отслеживание изменений в двух таблицах в моей базе данных SQL Server 2005. Мне нужно проверять добавления, удаления, обновления (с подробностями о том, что было обновлено). Я планировал использовать триггер, чтобы сделать это, но после поиска в Google я обнаружил, что это было невероятно легко сделать неправильно, и я хотел избежать этого на начальном этапе.

Кто-нибудь может опубликовать пример триггера обновления, который выполняет это успешно и элегантно? Я надеюсь закончить с таблицей аудита со следующей структурой:

Я БЫ
LogDate
TableName
TransactionType (обновить / вставить / удалить)
RecordID
FieldName
OldValue
NewValue

... но я открыт для предложений.

Спасибо!

34

sql-server triggers sql-server-2005 audit

Источник

user86191 26 дек '09 в 01:28

11 ответов

Решение

Мы используем ApexSQL Audit, который генерирует триггеры аудита, и ниже представлены структуры данных, используемые этим инструментом. Если вы не планируете покупать стороннее решение, вы можете установить этот инструмент в пробном режиме, посмотреть, как они реализовали триггеры и хранилище, а затем создать нечто подобное для себя.

Я не стал вдаваться в подробности того, как работают эти таблицы, но, надеюсь, это поможет вам начать.

19

Источник

user2160520 28 мар '13 в 10:15

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

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

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

Так, учитывая таблицу, как это

CREATE TABLE TestTable  
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,  
Name2 NVARCHAR(40))

Я бы создал таблицу аудита, подобную этой, в таблице аудита.

CREATE TABLE Audit.TestTable  
(SessionID UNIQUEIDENTIFER NOT NULL,  
ID INT NOT NULL,
Name1  NVARCHAR(40) NOT NULL,  
Name2  NVARCHAR(40),  
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),  
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),  
ChangedDate DATETIME NOT NULL Default GETDATE(),  
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())

И триггер для обновления, как это

CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS  
BEGIN  
    SET NOCOUNT ON
    DECLARE @SessionID UNIQUEIDENTIFER
    SET @SessionID = NEWID()
    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted

    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted

END

Это работает довольно быстро. Во время создания отчетов вы просто объединяете строки на основе sessionID и первичного ключа и создаете отчет. В качестве альтернативы вы можете создать пакетное задание, которое периодически просматривает все таблицы в таблице аудита и подготавливает пару имя-значение, показывающую изменения.

НТН

14

Источник

user35662 26 дек '09 в 15:26

Это выглядит просто и должно работать до тех пор, пока в ваших таблицах не появятся элементы image/varbinary и т. Д. У вас есть целая старая запись и вся новая запись в формате xml. Также должно работать правильно для вставки нескольких столбцов в 1 пакет.

CREATE TABLE _AuditTable
(Aud_Id int identity(1,1) primary key,
Aud_TableName varchar(100), 
Aud_ActionType char(1),
Aud_Username varchar(100),
Aud_OLDValues xml, 
Aud_NEWValues xml,
Aud_OperationDate datetime DEFAULT GETDATE()
)

И триггерный код

CREATE TRIGGER _test2_InsertUpdate on _test2
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
 IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) 
    RETURN;

declare @tablename varchar(100)
SELECT @tablename = OBJECT_NAME(parent_object_id) 
             FROM sys.objects 
             WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

/*Action*/
DECLARE @ActionType char(1)
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @ActionType = 'U'
       ELSE
               SELECT @ActionType = 'I'
ELSE
       SELECT @ActionType = 'D'

declare @inserted xml, @deleted xml 
SET @inserted = (SELECT * FROM inserted FOR XML PATH)
SET @deleted = (SELECT * FROM deleted FOR XML PATH)

             INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues)
             SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted
END

ВЫХОД

Aud_Id | Aud_TableName  | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues |   Aud_OperationDate
1      |_test2          |   I            |abc\mR        |   NULL        |<row><name>abc</name></row> |  2018-11-07 12:38:34.937

3

Источник

user1831734 07 ноя '18 в 11:50

Майк, мы используем инструмент www.auditdatabase.com, этот бесплатный инструмент генерирует триггеры аудита, и он хорошо работает с SQL Server 2008, 2005 и 2000. Это сложный инструмент, позволяющий настраивать триггеры аудита для таблицы.

Еще одним отличным инструментом является Apex SQL Audit

1

Источник

user531235 05 дек '10 в 14:24

Я наконец нашел универсальное решение, которое не требует динамического sql и регистрирует изменения всех столбцов.

Нет необходимости менять триггер, если таблица меняется.

Это журнал аудита:

CREATE TABLE [dbo].[Audit](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Type] [char](1) COLLATE Latin1_General_CI_AS NULL,
    [TableName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [PK] [int] NULL,
    [FieldName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [OldValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
    [NewValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
    [UpdateDate] [datetime] NULL,
    [Username] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_AuditB] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Это триггер для одной таблицы:

INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
      SELECT 
            CASE  WHEN NOT EXISTS (SELECT ID FROM deleted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'I' 
                WHEN NOT EXISTS (SELECT ID FROM inserted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'D' 
                  ELSE 'U' END as [Type],
            'AGB' as TableName, 
            ISNULL(ins.PK,del.PK) as PK,
            ISNULL(ins.FieldName,del.FieldName) as FieldName,
            del.FieldValue as OldValue,
            ins.FieldValue as NewValue,
            ISNULL(ins.Username,del.Username) as Username 
FROM (SELECT
      insRowTbl.PK,
      insRowTbl.Username,
      attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
      attr.insRow.value('.', 'nvarchar(max)') as FieldValue
  FROM (Select
            i.ID as PK,
            i.LastModifiedBy as Username,
            convert(xml, (select i.* for xml raw)) as insRowCol
        from inserted as i
       ) as insRowTbl
       CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)
  ) as ins
FULL OUTER JOIN (SELECT
      delRowTbl.PK,
      delRowTbl.Username,
      attr.delRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
      attr.delRow.value('.', 'nvarchar(max)') as FieldValue
  FROM (Select      
               d.ID as PK,
               d.LastModifiedBy as Username,
               convert(xml, (select d.* for xml raw)) as delRowCol
         from deleted as d
         ) as delRowTbl
        CROSS APPLY delRowTbl.delRowCol.nodes('/row/@*') as attr(delRow)
      ) as del
            on ins.PK = del.PK and ins.FieldName = del.FieldName
 WHERE 
      isnull(ins.FieldName,del.FieldName) not in ('LastModifiedBy', 'ID', 'TimeStamp') 
 and  ((ins.FieldValue is null and del.FieldValue is not null) 
      or (ins.FieldValue is not null and del.FieldValue is null) 
      or (ins.FieldValue != del.FieldValue))

Этот триггер предназначен для одной таблицы с именем AGB. Таблица с именем AGB имеет столбец первичного ключа с идентификатором имени и столбец с именем LastModifiedBy, в котором содержится имя пользователя, выполнившее последнее изменение.

Триггер состоит из двух частей, сначала он преобразует столбцы вставленных и удаленных таблиц в строки. Это подробно объясняется здесь: /questions/10330046/sql-server-stolbtsyi-v-stroki/10330065#10330065

Затем он объединяет строки (по одной строке на столбец) вставленных и удаленных таблиц по первичному ключу и имени поля и регистрирует строку для каждого измененного столбца. Он НЕ регистрирует изменения ID, TimeStamp или LastModifiedByColumn.

Вы можете вставить свое собственное имя таблицы, имена столбцов.

Вы также можете создать следующую хранимую процедуру, а затем вызвать эту хранимую процедуру, чтобы сгенерировать триггеры:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_create_audit_trigger]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[_create_audit_trigger] AS' 
END
ALTER PROCEDURE [dbo].[_create_audit_trigger]
     @TableName varchar(max),
     @IDColumnName varchar(max) = 'ID',
     @LastModifiedByColumnName varchar(max) = 'LastModifiedBy',
     @TimeStampColumnName varchar(max) = 'TimeStamp'
AS
BEGIN  

PRINT 'start ' + @TableName + ' (' + @IDColumnName + ', ' + @LastModifiedByColumnName + ', ' + @TimeStampColumnName + ')'

/* if you have other audit trigger on this table and want to disable all triggers, enable this: 
EXEC ('ALTER TABLE ' + @TableName + ' DISABLE TRIGGER ALL')*/

IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'tr_audit_'+@TableName)
    EXEC ('DROP TRIGGER [dbo].tr_audit_'+@TableName)


EXEC ('
CREATE TRIGGER [dbo].[tr_audit_'+@TableName+'] ON [ILSe].[dbo].['+@TableName+'] FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

      INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
      SELECT CASE  WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM deleted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''I'' WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM inserted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''D'' ELSE ''U'' END as [Type],
        '''+@TableName+''' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM 
      (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select      
                  i.'+@IDColumnName+' as PK,
                  i.'+@LastModifiedByColumnName+' as Username,
                  convert(xml, (select i.* for xml raw)) as insRowCol
                from inserted as i) as insRowTbl
                CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow)) as ins
            FULL OUTER JOIN 
      (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.delRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select      
                  d.'+@IDColumnName+' as PK,
                  d.'+@LastModifiedByColumnName+' as Username,
                  convert(xml, (select d.* for xml raw)) as delRowCol
                from deleted as d) as delRowTbl
                CROSS APPLY delRowTbl.delRowCol.nodes(''/row/@*'') as attr(delRow)) as del on ins.PK = del.PK and ins.FieldName = del.FieldName
    WHERE isnull(ins.FieldName,del.FieldName) not in ('''+@LastModifiedByColumnName+''', '''+@IDColumnName+''', '''+@TimeStampColumnName+''') and
    ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue))

END
')

PRINT 'end ' + @TableName

PRINT ''

END

1

Источник

user4160788 05 май '17 в 08:39

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

Если вам нравится этот подход, возможно, вам стоит использовать этот триггер и заменить некоторые общие шаги сгенерированным кодом для каждой таблицы отдельно.

Тем не менее я создал полностью общий Audit-Trigger. Наблюдаемая таблица должна иметь PK, но этот PK может даже быть многостолбцовым.

Некоторые типы столбцов (например, BLOB) могут не работать, но вы можете легко исключить их.

Это не будет лучшим по производительности:-D

Если честно: это скорее упражнение...

SET NOCOUNT ON;
GO
CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER
                      ,LogDate DATETIME
                      ,TableSchema VARCHAR(250)
                      ,TableName VARCHAR(250)
                      ,AuditType VARCHAR(250),Content XML);
GO

- Несколько таблиц для проверки этого (специально использовали необычные столбцы PK)

CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL
                      ,ID2 UNIQUEIDENTIFIER NOT NULL
                      ,Test1 VARCHAR(100)
                      ,Test2 DATETIME);
--Add a two column PK
ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);

- Некоторые данные испытаний

INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
 ({d'2000-01-01'},NEWID(),'Test1',NULL)
,({d'2000-02-01'},NEWID(),'Test2',{d'2002-02-02'});

- Это текущий контент

SELECT * FROM dbo.Testx;
GO

- Триггер для аудита

    CREATE TRIGGER [dbo].[UpdateTestTrigger]
    ON [dbo].[Testx]
    FOR UPDATE,INSERT,DELETE
    AS 
    BEGIN

        IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;

        SET NOCOUNT ON;
        DECLARE @tableSchema VARCHAR(250);
        DECLARE @tableName   VARCHAR(250);
        DECLARE @AuditID UNIQUEIDENTIFIER=NEWID();
        DECLARE @LogDate DATETIME=GETDATE();

        SELECT @tableSchema = sch.name
              ,@tableName   = tb.name
        FROM sys.triggers AS tr
        INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id 
        INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id
        WHERE tr.object_id = @@PROCID

       DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN 'upd'
                               ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN 'del' ELSE 'ins' END END;

       SELECT * INTO #tmpInserted FROM inserted;
       SELECT * INTO #tmpDeleted FROM deleted;

       SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME
       INTO #tmpPKColumns
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG
                                                            AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA
                                                            AND tc.TABLE_NAME=kc.TABLE_NAME
                                                            AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME
                                                            AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
       WHERE tc.TABLE_SCHEMA=@tableSchema
         AND tc.TABLE_NAME=@tableName
       ORDER BY kc.ORDINAL_POSITION;

       DECLARE @pkCols VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT 'UNION ALL SELECT ''' + pc.COLUMN_NAME + ''' AS [@name] , CAST(COALESCE(i.' + QUOTENAME(pc.COLUMN_NAME) + ',d.' + QUOTENAME(pc.COLUMN_NAME) + ') AS VARCHAR(MAX)) AS [@value] '
        FROM #tmpPKColumns AS pc
        ORDER BY pc.ORDINAL_POSITION
        FOR XML PATH('')
       ),1,16,'');

       DECLARE @pkColsCompare VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT 'AND i.' + QUOTENAME(pc.COLUMN_NAME) + '=d.' + QUOTENAME(pc.COLUMN_NAME) 
        FROM #tmpPKColumns AS pc
        ORDER BY pc.ORDINAL_POSITION
        FOR XML PATH('')
       ),1,3,'');

       DECLARE @cols VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT ',' + CASE WHEN @tp='upd' THEN 
               'CASE WHEN (i.[' + COLUMN_NAME + ']!=d.[' + COLUMN_NAME + '] ' +
               'OR (i.[' + COLUMN_NAME + '] IS NULL AND d.[' + COLUMN_NAME + '] IS NOT NULL) ' + 
               'OR (i.['+ COLUMN_NAME + '] IS NOT NULL AND d.[' + COLUMN_NAME + '] IS NULL)) ' +
               'THEN ' ELSE '' END +
               '(SELECT ''' + COLUMN_NAME + ''' AS [@name]' + 
                             CASE WHEN @tp IN ('upd','del') THEN ',ISNULL(CAST(d.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@old]' ELSE '' END + 
                             CASE WHEN @tp IN ('ins','upd') THEN ',ISNULL(CAST(i.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@new] ' ELSE '' END + 
                      ' FOR XML PATH(''Column''),TYPE) ' + CASE WHEN @tp='upd' THEN 'END' ELSE '' END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA=@tableSchema AND TABLE_NAME=@tableName
        FOR XML PATH('')
       ),1,1,''
       );

        DECLARE @cmd VARCHAR(MAX)=   
        'SET LANGUAGE ENGLISH;
        WITH ChangedColumns AS
        (
        SELECT   A.PK' +
               ',A.PK.query(''data(/PK/Column/@value)'').value(''text()[1]'',''nvarchar(max)'') AS PKVals' +
               ',Col.*  
        FROM #tmpInserted AS i
        FULL OUTER JOIN #tmpDeleted AS d ON ' + @pkColsCompare +
       ' CROSS APPLY
        (
            SELECT ' + @cols + ' 
            FOR XML PATH(''''),TYPE
        ) AS Col([Column])
        CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT ' + @pkCols + ') AS tbl FOR XML PATH(''Column''), ROOT(''PK''),TYPE)) AS A(PK)
        )
        INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content)
        SELECT  ''' + CAST(@AuditID AS VARCHAR(MAX)) + ''',''' + CONVERT(VARCHAR(MAX),@LogDate,126) + ''',''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
        ,(
        SELECT ''' + @tableSchema + ''' AS [@TableSchema]
                ,''' + @tableName + ''' AS [@TableName]
                ,''' + @tp + ''' AS [@ActionType]
        ,(
            SELECT ChangedColumns.PK AS [*]
            ,(
            SELECT x.[Column] AS [*],''''
            FROM ChangedColumns AS x 
            WHERE x.PKVals=ChangedColumns.PKVals
            FOR XML PATH(''Values''),TYPE
            )
            FROM ChangedColumns
            FOR XML PATH(''Row''),TYPE
            )
        FOR XML PATH(''Changes'')
        );';

        EXEC (@cmd);

       DROP TABLE #tmpInserted;
       DROP TABLE #tmpDeleted;
    END
    GO

- Теперь давайте проверим это с некоторыми операциями:

UPDATE dbo.Testx SET Test1='New 1' WHERE ID1={d'2000-01-01'};
UPDATE dbo.Testx SET Test1='New 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Testx WHERE ID1={d'2000-02-01'};
DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect
INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
 ({d'2000-03-01'},NEWID(),'Test3',{d'2001-03-03'})
,({d'2000-04-01'},NEWID(),'Test4',{d'2001-04-04'})
,({d'2000-05-01'},NEWID(),'Test5',{d'2001-05-05'});
UPDATE dbo.Testx SET Test2=NULL; --all rows
DELETE FROM dbo.Testx WHERE ID1 IN ({d'2000-02-01'},{d'2000-03-01'});
GO

- Проверить окончательный статус

SELECT * FROM dbo.Testx;
SELECT * FROM AuditTest;
GO

- Очистить (осторожно с реальными данными!)

DROP TABLE dbo.Testx;
GO
DROP TABLE dbo.AuditTest;
GO

Результат вставки

<Changes TableSchema="dbo" TableName="Testx" ActionType="ins">
  <Row>
    <PK>
      <Column name="ID1" value="May  1 2000 12:00AM" />
      <Column name="ID2" value="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
    </PK>
    <Values>
      <Column name="ID1" new="May  1 2000 12:00AM" />
      <Column name="ID2" new="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
      <Column name="Test1" new="Test5" />
      <Column name="Test2" new="May  5 2001 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Apr  1 2000 12:00AM" />
      <Column name="ID2" value="28625CE7-9424-4FA6-AEDA-1E4853451655" />
    </PK>
    <Values>
      <Column name="ID1" new="Apr  1 2000 12:00AM" />
      <Column name="ID2" new="28625CE7-9424-4FA6-AEDA-1E4853451655" />
      <Column name="Test1" new="Test4" />
      <Column name="Test2" new="Apr  4 2001 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Mar  1 2000 12:00AM" />
      <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
    </PK>
    <Values>
      <Column name="ID1" new="Mar  1 2000 12:00AM" />
      <Column name="ID2" new="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
      <Column name="Test1" new="Test3" />
      <Column name="Test2" new="Mar  3 2001 12:00AM" />
    </Values>
  </Row>
</Changes>

Выборочный результат обновления

<Changes TableSchema="dbo" TableName="Testx" ActionType="upd">
  <Row>
    <PK>
      <Column name="ID1" value="Feb  1 2000 12:00AM" />
      <Column name="ID2" value="D7AB263A-EEFC-47DB-A6BB-A559FE8F2119" />
    </PK>
    <Values>
      <Column name="Test1" old="Test2" new="New 1" />
      <Column name="Test2" old="Feb  2 2002 12:00AM" new="Jan  1 2000 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Jan  1 2000 12:00AM" />
      <Column name="ID2" value="318C0A66-8833-4F03-BCEF-7AB78C91704F" />
    </PK>
    <Values>
      <Column name="Test2" old="##NULL##" new="Jan  1 2000 12:00AM" />
    </Values>
  </Row>
</Changes>

И результат удаления

<Changes TableSchema="dbo" TableName="Testx" ActionType="del">
  <Row>
    <PK>
      <Column name="ID1" value="Mar  1 2000 12:00AM" />
      <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
    </PK>
    <Values>
      <Column name="ID1" old="Mar  1 2000 12:00AM" />
      <Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
      <Column name="Test1" old="Test3" />
      <Column name="Test2" old="##NULL##" />
    </Values>
  </Row>
</Changes>

1

Источник

user5089204 18 авг '17 в 23:22

Я добавлю свой подход и предложения к соединению.

У меня есть таблица, очень похожая на предложенный вами проект, который я использовал в течение последних семи лет в базе данных SQL 2005 (теперь 2008).

Я добавил триггеры вставки, обновления и удаления в выбранные таблицы, а затем проверил наличие изменений в выбранных полях. В то время это было просто и хорошо работает.

Вот проблемы, которые я нахожу с этим подходом:

Поля старых / новых значений таблицы аудита должны быть типов varchar(MAX), чтобы иметь возможность обрабатывать все различные значения, которые могут быть проверены: int, bool, decimal, float, varchar и т. Д. Все должны соответствовать
Код для проверки каждого поля является утомительным для написания сопровождения. Также легко пропустить некоторые вещи (например, изменение пустого поля на значение, которое не было поймано, потому что NULL!= Value равно NULL.
Удалить запись: как вы записываете это? Все поля? Избранные? Это становится сложным

Мое будущее видение заключается в том, чтобы использовать некоторый код SQL-CLR и написать общий триггер, который выполняется и проверяет метаданные таблицы, чтобы увидеть, что нужно проверять. Во-вторых, новые / старые значения будут преобразованы в поля XML, и весь объект будет записан: это приведет к увеличению количества данных, но удаление будет иметь целую запись. В Интернете есть несколько статей о триггерах аудита XML.

0

Источник

user41403 30 май '13 в 17:27

Триггер используется для того, чтобы при изменении или вставке в конкретную таблицу это выполнялось, и вы можете проверить определенный столбец в триггере. Полный пример с объяснением находится на следующем веб-сайте. http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

0

Источник

user4652668 13 апр '15 в 10:55

CREATE TRIGGER TriggerName 
ON TableName 
FOR INSERT, UPDATE, DELETE AS 
BEGIN
 SET NOCOUNT ON

 DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

 CREATE TABLE #inputbuffer 
 (
  EventType nvarchar(30), 
  Parameters int, 
  EventInfo nvarchar(255)
 )

 SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

 INSERT INTO #inputbuffer 
 EXEC (@ExecStr)

 SET @Qry = (SELECT EventInfo FROM #inputbuffer)

 SELECT @Qry AS 'Query that fired the trigger', 
 SYSTEM_USER as LoginName, 
 USER AS UserName, 
 CURRENT_TIMESTAMP AS CurrentTime
END

0

Источник

user2482160 13 июн '13 в 12:05

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

CREATE TABLE [dbo].[Audit](
    [TYPE] [CHAR](1) NULL,
    [TableName] [VARCHAR](128) NULL,
    [PK] [VARCHAR](1000) NULL,
    [FieldName] [VARCHAR](128) NULL,
    [OldValue] [VARCHAR](1000) NULL,
    [NewValue] [VARCHAR](1000) NULL,
    [UpdateDate] [datetime] NULL,
    [UserName] [VARCHAR](128) NULL
) ON [PRIMARY]

-9

Источник

user1865565 05 дек '12 в 12:38

Другие вопросы по тегам sql-server triggers sql-server-2005 audit

user105929 26 дек '09 в 17:19 2009-12-26 17:19 · Accepted Answer · 2009-12-26 17:19

Я просто хочу напомнить пару моментов:

Использование генераторов кода У вас не может быть единой процедуры для отслеживания всех таблиц, вам нужно будет генерировать похожие, но разные триггеры для каждой отслеживаемой таблицы. Этот вид работы лучше всего подходит для автоматической генерации кода. На вашем месте я бы использовал XSLT-преобразование для генерации кода из XML, и XML можно генерировать автоматически из метаданных. Это позволяет вам легко поддерживать триггеры, восстанавливая их каждый раз, когда вы вносите изменения в логику / структуру аудита или добавляется / изменяется целевая таблица.

Рассмотрим планирование потенциала для аудита. Таблица аудита, которая отслеживает все изменения значений, безусловно, будет самой большой таблицей в базе данных: она будет содержать все текущие данные и всю историю текущих данных. Такая таблица увеличит размер базы данных на 2-3 порядка (х10, х100). И таблица аудита быстро станет узким местом всего:

каждая операция DML потребует блокировки в таблице аудита
все административные и эксплуатационные операции должны соответствовать размеру базы данных из-за аудита

Учитывайте изменения схемы. Таблица с именем "Foo" может быть удалена, а позже может быть создана другая таблица с именем "Foo". Контрольный журнал должен уметь различать два разных объекта. Лучше использовать подход с медленным изменением размеров.

Рассмотрите необходимость эффективного удаления записей аудита. Когда истекает срок хранения, заданный вашими предметными политиками приложения, вы должны иметь возможность удалить записи аудита. Сейчас это может показаться не таким уж большим делом, но через 5 лет, когда появятся первые записи, таблица аудита выросла до 9,5 ТБ, это может стать проблемой.

Рассмотрим необходимость запроса аудита. Структура таблицы аудита должна быть подготовлена, чтобы эффективно отвечать на запросы по аудиту. Если ваш аудит не может быть запрошен, тогда он не имеет значения. Запросы будут полностью зависеть от ваших требований, и только вы их знаете, но большинство записей аудита запрашиваются по временным интервалам ("какие изменения произошли между 7 и 8 вечера вчера?"), По объектам ("какие изменения произошли в этой записи в этом таблица?') или по автору (' какие изменения внес Боб в базу данных?').