Таблицы истории профи, минусы и gotchas - использование триггеров, sproc или на уровне приложения

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

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

Триггеры в главной таблице для обновления, вставки и удаления. (Базы данных)
Сохраненные процедуры. (Базы данных)
Уровень приложения. (Приложение)

Мой главный вопрос: какие плюсы, минусы и gotchas выполняют работу в каждом из этих слоев.

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

Ответ 1

Я бы сказал так:

Сохраненные procs: они обойдены, если вы измените таблицу напрямую. Безопасность в базе данных может управлять этим
Приложение: та же сделка. Кроме того, если у вас есть несколько приложений, возможно, на разных языках, их необходимо реализовать в каждом стеке, что несколько избыточно; и
Триггеры: прозрачны для приложения и будут фиксировать все изменения. Это мой предпочтительный метод.

Ответ 2

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

Для тех, кто считает, что триггеры более безопасны, чем sprocs, потому что их нельзя обойти, я напоминаю им, что они делают следующее предположение:

!) Существуют разрешения, запрещающие пользователям выполнять DISABLE TRIGGER [но тогда могут существовать разрешения для ограничения всего доступа к базе данных, кроме EXECUTE на sprocs, который является общим для корпоративных приложений] - поэтому необходимо принимать правильные разрешения и поэтому sprocs равны триггерам с точки зрения безопасности и способности обходить

!) В зависимости от базы данных возможно выполнение операторов обновления, которые не запускают триггеры. Я мог бы воспользоваться знанием глубины выполнения вложенных триггеров, чтобы обойти триггер. Единственное надежное решение включает безопасность в базе данных и ограничение доступа к данным с использованием только одобренных механизмов - будь то триггеры, sprocs или уровни доступа к данным.

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

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

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

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

Ответ 3

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

Триггеры на сильно используемом (скажем, многопользовательском приложении SaaS) могут быть чрезвычайно дорогими
В некоторых сценариях несколько полей могут быть избыточными. Триггеры хороши только тогда, когда вы четко читаете поля, которые должны быть зарегистрированы; хотя при использовании приложения у вас может быть уровень перехватчика, который может помочь вам регистрировать определенные поля на основе "конфигурации"; хотя с его собственной долей накладных расходов
Без соответствующего управления базой данных человек может легко отключить триггеры, изменить данные и включить триггеры; все без привлечения каких-либо аварийных сигналов
В случае веб-приложений, где соединения устанавливаются из пула, отслеживание фактических пользователей, внесших изменения, может быть утомительным. Возможным решением было бы иметь поле "EditedBy" в каждой таблице транзакций.

Ответ 4

Позднее, но он добавляет еще несколько вариантов, которые можно рассмотреть.

Изменить захват данных: Эта функция доступна в SQL Server 2008 R2 +, но только в корпоративной версии. Он позволяет выбирать таблицы, которые вы хотите отслеживать, и SQL Server выполнит эту работу за вас. Он работает, читая журнал транзакций и заполняя таблицы истории данными.

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

Даунсайд - это то, что по умолчанию это не поддерживается. Параметры должны читать журнал транзакций с использованием недокументированных функций, таких как fn_dblog или сторонних инструментов, таких как ApexSQL Log.

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

Все они работают на уровне базы данных и полностью прозрачны для приложений.

Ответ 5

Триггеры - единственный надежный способ захвата изменений. Если вы делаете это в Хранимой Procs или в приложении, вы всегда можете войти и SQL отменить изменение, в котором у вас нет журнала (непреднамеренно). Конечно, кто-то, кто не хочет покидать журнал, может отключить триггеры. Но вы скорее вынуждаете кого-то отключать ведение журнала, чем надеяться, что они не забыли его включить.

Ответ 6

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

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