Подтвердить что ты не робот

Таблица истории SQL Server - заполняется через SP или Trigger?

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

Все мое приложение использует хранимые процедуры, нет встроенного SQL. Единственное соединение с базой данных для изменения этих таблиц будет осуществляться через приложение и интерфейс SP. Традиционно магазины, с которыми я работал, выполнили эту задачу с помощью триггеров.

Если у меня есть выбор между хранимыми процедурами и триггерами, что лучше? Что быстрее?

4b9b3361

ОТВЕТЫ

Ответ 1

Триггеры.

Мы написали GUI (внутренне называемый Red Matrix Reloaded), чтобы упростить создание/управление триггерами ведения журнала аудита.

Здесь некоторый DDL используемого материала:

Таблица AuditLog

CREATE TABLE [AuditLog] (
    [AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
    [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
    [RowGUID] [uniqueidentifier] NOT NULL ,
    [ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UserGUID] [uniqueidentifier] NULL ,
    [TagGUID] [uniqueidentifier] NULL ,
    [Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

Триггер для входа в журнал

CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - INSERTED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)

    SELECT
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'INSERTED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Inserted i

Триггер для регистрации обновлений

CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /* ParentNodeGUID uniqueidentifier */
    IF UPDATE (ParentNodeGUID)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'ParentNodeGUID', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.ParentNodeGUID, --OldValue
            i.ParentNodeGUID --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
        OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
        OR (d.ParentNodeGUID <> i.ParentNodeGUID)
    END

    /* Caption varchar(255) */
    IF UPDATE (Caption)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'Caption', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.Caption, --OldValue
            i.Caption --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
        OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
        OR (d.Caption <> i.Caption)
    END

...

/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
    INSERT INTO AuditLog(
        ChangeDate, RowGUID, ChangeType, 
        Username, HostName, AppName,
        UserGUID, 
        TableName, FieldName, 
        TagGUID, Tag, 
        OldValue, NewValue)
    SELECT 
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'UPDATED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        'ImageGUID', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        (SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
        (SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
    FROM Inserted i
        INNER JOIN Deleted d
        ON i.NodeGUID = d.NodeGUID
    WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
    OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
    OR (d.ImageGUID <> i.ImageGUID)
END

Триггер для регистрации Delete

CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - DELETED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue,NewValue)

    SELECT
        getdate(), --ChangeDate
        d.NodeGUID, --RowGUID
        'DELETED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        d.ParentNodeGUID, --TagGUID
        d.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Deleted d

И чтобы узнать, какой пользователь в программном обеспечении сделал обновление, каждое соединение "регистрируется на SQL Server", вызывая хранимую процедуру:

CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS

/* Saves the given UserGUID as the session "Context Information" */
IF @UserGUID IS NULL
BEGIN
    PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
    DECLARE @BinVar varbinary(128)
    SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
    SET CONTEXT_INFO @BinVar
    RETURN 0
END

DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary


/* To load the guid back 
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

select @SavedUserGUID AS UserGUID
*/

Примечания

  • Формат кода Stackoverflow удаляет большинство пустых строк - поэтому форматирование отстой
  • Мы используем таблицу пользователей, а не интегрированную безопасность.
  • Этот код предоставляется в качестве уверенности - не допускается критика нашего выбора дизайна. Purists могут настаивать на том, что весь код регистрации должен быть выполнен на бизнес-уровне - они могут прийти сюда и написать/сохранить его для нас.
  • blobs не могут быть зарегистрированы с помощью триггеров в SQL Server (нет версии "до" blob - есть только то, что есть). Текст и nText - это blobs, что делает заметки невоспроизводимыми или делает их varchar (2000).
  • Столбец "Тег" используется как произвольный текст для идентификации строки (например, если клиент был удален, тег будет показывать "General Motors North America" в таблице журнала аудита.
  • TagGUID используется для указания строки "parent". Например, запись InvoiceLineItems указывает на InvoiceHeader. Таким образом, любой, кто ищет записи журнала аудита, связанные с конкретным счетом, найдет удаленные "позиции" в теге TagGUID позиции в контрольном журнале.

  • иногда значения "OldValue" и "NewValue" записываются как подвыбор - чтобы получить значимую строку. то есть ".

    OldValue: {233d-ad34234..} NewValue: {883-sdf34...}

менее полезен в контрольном журнале, чем:

OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management

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

Ответ 2

в SQL Server 2008 может помочь новая функция, называемая CDC (Change data Capture) CDC на MSDN. CDC - это возможность записывать изменения в табличные данные в другую таблицу без написания триггеров или какого-либо другого механизма. Изменение записи данных записывает изменения, такие как вставка, обновление и удаление в таблицу на SQL-сервере, тем самым делая информацию об изменениях, доступных в реляционных формат.

Видео Channel9

Ответ 3

У нас есть сторонний инструмент ApexSQL Audit, который мы использовали для создания триггеров.

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

Таблица 1 - содержит данные транзакции (кто, когда, приложение, имя хоста и т.д.)

CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
    [AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
    [DATABASE] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](261) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](261) NOT NULL,
    [AUDIT_ACTION_ID] [tinyint] NOT NULL,
    [HOST_NAME] [varchar](128) NOT NULL,
    [APP_NAME] [varchar](128) NOT NULL,
    [MODIFIED_BY] [varchar](128) NOT NULL,
    [MODIFIED_DATE] [datetime] NOT NULL,
    [AFFECTED_ROWS] [int] NOT NULL,
    [SYSOBJ_ID]  AS (object_id([TABLE_NAME])),
  PRIMARY KEY CLUSTERED 
  (
       [AUDIT_LOG_TRANSACTION_ID] ASC
  )
)

Таблица 2 - содержит значения до/после.

CREATE TABLE [dbo].[AUDIT_LOG_DATA](
   [AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
   [AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
   [PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
   [COL_NAME] [nvarchar](128) NOT NULL,
   [OLD_VALUE_LONG] [ntext] NULL,
   [NEW_VALUE_LONG] [ntext] NULL,
   [NEW_VALUE_BLOB] [image] NULL,
   [NEW_VALUE]  AS (isnull(CONVERT([varchar](8000),      [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
   [OLD_VALUE]  AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
   [PRIMARY_KEY]  AS ([PRIMARY_KEY_DATA]),
   [DATA_TYPE] [char](1) NOT NULL,
   [KEY1] [nvarchar](500) NULL,
   [KEY2] [nvarchar](500) NULL,
   [KEY3] [nvarchar](500) NULL,
   [KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED 
 (
    [AUDIT_LOG_DATA_ID] ASC
)
)

Вставить триггер

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

CREATE TRIGGER [dbo].[tr_i_AUDIT_Audited_Table]
ON [dbo].[Audited_Table]
FOR INSERT
NOT FOR REPLICATION
As
BEGIN
DECLARE 
    @IDENTITY_SAVE              varchar(50),
    @AUDIT_LOG_TRANSACTION_ID       Int,
    @PRIM_KEY               nvarchar(4000),
    @ROWS_COUNT             int

SET NOCOUNT ON
Select @ROWS_COUNT=count(*) from inserted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
    TABLE_NAME,
    TABLE_SCHEMA,
    AUDIT_ACTION_ID,
    HOST_NAME,
    APP_NAME,
    MODIFIED_BY,
    MODIFIED_DATE,
    AFFECTED_ROWS,
    [DATABASE]
)
values(
    'Audited_Table',
    'dbo',
    2,  --  ACTION ID For INSERT
    CASE 
      WHEN LEN(HOST_NAME()) < 1 THEN ' '
      ELSE HOST_NAME()
    END,
    CASE 
      WHEN LEN(APP_NAME()) < 1 THEN ' '
      ELSE APP_NAME()
    END,
    SUSER_SNAME(),
    GETDATE(),
    @ROWS_COUNT,
    'Database_Name'
)

Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()    

--This INSERT INTO code is repeated for each columns that is audited. 
--Below are examples for only two columns
INSERT INTO dbo.AUDIT_LOG_DATA
(
    AUDIT_LOG_TRANSACTION_ID,
    PRIMARY_KEY_DATA,
    COL_NAME,
    NEW_VALUE_LONG,
    DATA_TYPE
    , KEY1
)
SELECT
    @AUDIT_LOG_TRANSACTION_ID,
    convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
    'Column1',
    CONVERT(nvarchar(4000), NEW.[Column1], 0),
    'A'
    , CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column1] Is Not Null

 --value is inserted for each column that is selected for auditin
INSERT INTO dbo.AUDIT_LOG_DATA
(
    AUDIT_LOG_TRANSACTION_ID,
    PRIMARY_KEY_DATA,
    COL_NAME,
    NEW_VALUE_LONG,
    DATA_TYPE
    , KEY1
)
SELECT
    @AUDIT_LOG_TRANSACTION_ID,
    convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
    'Column2',
    CONVERT(nvarchar(4000), NEW.[Column2], 0),
    'A'
    , CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
    FROM inserted NEW
    WHERE NEW.[Column2] Is Not Null
End

Отказ от ответственности: я не связан с Apex каким-либо образом, но я использую их инструменты в своей текущей работе.

Ответ 4

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

Как быстрее? Определение того, что быстро находится внутри базы данных, является трудной проблемой при большом числе переменных. Если вы не попробуете его в обоих направлениях и сравните, вы не получите полезный ответ на какой метод быстрее. Переменные включают размер задействованных таблиц, нормальный шаблон обновлений, скорость дисков на сервере, объем памяти, объем памяти, предназначенный для кэширования, и т.д. Этот список бесконечен, и каждая переменная влияет на триггеры быстрее, чем пользовательский SQL внутри SP.

Хорошо. Быстро. Дешевые. Выбери два. Триггеры хороши с точки зрения целостности и, вероятно, дешево с точки зрения обслуживания. Возможно, они также Быстры в том, что как только они работают, вы закончите с ними. SP - проблема обслуживания, а продвижение материала в обслуживание может быть быстрым, но никогда не бывает хорошим или дешевым.

Удачи.

Ответ 5

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

Для захвата каждой операции используйте либо ПОСЛЕ ТРИГГЕРОВ, либо Смена данных.

После того, как триггеры предоставят вам две временные таблицы для работы внутри триггера:

  • INSERTED после INSERT или UPDATE
  • DELETED после DELETE

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

Change Data Capture (CDC) предназначен для создания таблицы дельта, которую вы можете использовать в качестве источника для загрузки данных в хранилище данных (или таблицу истории). В отличие от триггеров, CDC является асинхронным, и вы можете использовать любой метод и планирование для заполнения вашего пункта назначения (sprocs, SSIS).

Вы можете получить как исходные данные, так и изменения с помощью CDC. Изменить отслеживание (CT) обнаруживает только измененные строки. Можно построить полный контрольный журнал с CDC, но не с КТ. CDC и CT доступны только в выпусках MSSQL 2008 Enterprise и Developer Edition.

Ответ 6

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

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

Ответ 7

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

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

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

И для этого типа аудита вы не хотите использовать триггеры. Чем выше в слое BR вы вставляете генерации этих событий, тем лучше.

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

Ответ 8

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

CREATE TRIGGER [dbo].[tr_Employee_rev]
ON [dbo].[Employee]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    IF EXISTS(SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'u', GetDate(), SYSTEM_USER FROM INSERTED
    END 

    IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'i', GetDate(), SYSTEM_USER FROM INSERTED
    END

    IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT deleted.ID, deleted.Firstname,deleted.Initial,deleted.Surname,deleted.Birthdate,'d', GetDate(), SYSTEM_USER FROM DELETED 
    END
END

Я использую SQLServer для генерации SQL для таблиц ревизий вместо ручного кодирования. Этот код доступен на https://github.com/newdigate/sqlserver-revision-tables

Ответ 9

Триггеры. Прямо сейчас вы можете сказать, что единственный способ обновления данных - через ваши SP, но все может измениться, или вам может понадобиться массовая вставка/обновление, что использование SP будет слишком громоздким. Пойдите с триггерами.

Ответ 10

Это зависит от характера приложения и структуры таблицы, количества индексов, размера данных и т.д., внешних ключей и т.д. Если это относительно простые таблицы (нет или несколько индексов, таких как индексы столбцов datetime/integer), с ограниченный набор данных (< 1 миллион строк), вы, вероятно, будете в порядке использовать триггеры.

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

В этом случае все зависит от табличных индексов. Мы используем Sql Server 2000 для 24/7 приложений, которые обрабатывают более 100 тыс. Финансовых транзакций в день. Самая большая/основная таблица имеет более 100 миллионов строк и 15 индексов (массовые удаления не являются разумно возможными, если требуется время безотказной работы). Несмотря на то, что все SQL выполняется в хранимых процедурах, мы не используем триггеры или внешние ключи из-за повышения производительности.

Ответ 11

Триггеры. Вот мой подход:

  • Создайте одну таблицу аудита для каждой критической таблицы, требующей аудиторской проверки
  • Таблица аудита будет включать в себя все столбцы из исходной таблицы + записи записей аудита столбца, такие как who, when и action
  • Триггер только для UPDATE и DELETE, операция INSERT будет иметь первоначальную запись в самой исходной таблице
  • Перед обновлением или удалением скопируйте исходную запись + информацию аудита в таблицу аудита.
  • (Необязательно - только для UPDATE:) Чтобы узнать, какой столбец обновлен, используйте либо UPDATE (ColumnName), либо COLUMNS_UPDATED(), встроенные в функцию SQL, чтобы определить затронутые столбцы

Аудит таким образом сохраняет текущий статус в исходной таблице и всей истории в таблице аудита и легко идентифицируется ключевыми столбцами.