MS SQL Server. T-SQL. Организация "истории изменений"
Захотелось мне сделать так, чтобы любые изменения какой-либо записи в определенной таблице отслеживались и всегда можно было откатиться к более старой версии.
Поразмыслив я прикнул, что хорошо бы реализовать это на уровне БД, чтобы исключить какие-либо накладки в приложении.
Создаем mytable_history, с сигнатурой идентичной mytable, дополняем полями historyID и historyDate, вешаем на mytable триггеры, которые при вставке и изменении копируют соответствующую запись в history с новым historyID, и проставляют дату изменения.
Решение устраивает. Любое изменение всегда будет отражено, а старая версия сохранена даже если взломан сайт — доступа у аккаунта по кторому сайт ходит в БД к таблице _history нет.
И тогда захотелось большего, знать какой пользователь системы сделал то или иное изменение. Но на уровне базы данных это неизвестно. Идентификатор пользователя известен только на уровне приложения, для базы данных же они все «на одно лицо» и ходят под общим аккаунтом к sql серверу.
Первой мыслью было при коннекте создавать переменную с идентификатором пользователя и использовать её в триггерах. Но одно и тоже соединение могут использовать разные пользователи сайта — ASP.NET держит в пуле n-e количество соединений и выдает по необходимости, так что это не годится. Значит нужно передавать идентификатор в каждом запросе к БД.
Собственно вопросы:
1) правилен ли вообще такой дизайн? Может то что я хочу по-другому делают? Но не хочется всетаки выносить систему версий из БД.
2) как можно к запросу прикрепить переменную, так чтобы она не влияла на запрос, но была доступна триггеру AFTER UPDATE.
Используется linq2sql, т.е. теоретически можно сделать свой DataContext и сделать отправку идентификатора пользователя с каждым запросом. Вопрос только как это можно реализовать?
По первой части вопроса могу сказать — да, дизайн вполне нормальный. Я использую примерно такой же подход (СУБД Oracle).
По части отслеживания того, какой конкретно юзер сделал определенное изменение — это зависит от того, как вы храните информацию о юзерах. Если юзеры хранятся в базе в отдельной табличке, как я предполагаю, то можно делать так.
Добавьте колонку last_modified_by_user на таблицу mytable, и, соответственно, таблицу mytable_history.
Соответственно, когда какой-то пользователь выполняет операцию (insert / update / delete), вы выставляете ID-шник этого юзера, и при срабатывании вашего after-insert, after-update, after-delete row-level триггера этот айдишник перенесется в таблицу аудита.
А как вставлять айдишник пользователя изначально в таблицу mytable? Ну, создавать server-side user-context в котором хранится имя-id пользователя, ассоциированный с пользовательской сессий, и при выполнении запроса с уровня бизнес логики — заполнять это поле.
Спасибо за ответ. Примерно так я и решил, только вместо триггеров перешел на хранимые процедуры, это позволило сделать все необходимые изменения в приложении на уровне linq2sql entities не внося абсолютно никаких правок в бизнес логику. Вот подумываю написать об этом топик.
