Главная » Microsoft SQL Server, Базы данных » Логическое удаление данных

0

Для усиления поддержки целостности данных многие разработчики препятствуют физическому удалению информации — они заменяют его логическим удалением. Наиболее распространенным методом является создание специального битового столбца флага удаления. Когда пользователь удаляет некоторую строку из клиентского приложения, триггер маркирует ее как удаленную, устанавливая флаг в значение true. Флаг логического удаления может быть реализован несколькими способами.

?               Его установку может выполнять само клиентское приложение.

?               Эта задача может быть возложена на хранимую процедуру.

?               Триггер INSTEAD OF может перехватывать инструкции DELETE и вместо физического удаления устанавливать флаг.

Флаг логического удаления не стоит рассматривать как какой-то продвинутый На заметку метод. Еще СУБД DBASE III использовала этот флаг для маркировки удаленных строк, а физическое удаление осуществляла только при операции сжатия файла.

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

Триггеры логического удаления

Триггеры INSTEAD OF реализуют систему логического удаления на уровне таблицы и гарантируют ее постоянную функциональность. Эти триггеры преследуют две цели: преобразование физического удаления в логическое и реализацию некоторого метода реального физического удаления строк.

Предлагаемый триггер позволяет пользователю sa физически удалить любую строку, таким образом предоставляя метод очистки базы данных. Триггер INSTEAD OF не имеет рекурсивного действия, так что содержащаяся в нем инструкция DELETE не приведет к повторному его вызову и будет выполнена. Первая команда в данном примере изменяет структуру таблицы Product, добавляя в нее битовый флаг IsDeleted:

ALTER TABLE Product

ADD IsDeleted BIT NOT NULL DEFAULT 0

CREATE Trigger Product_LogicalDelete On dbo.Product INSTEAD OF Delete AS

IF (suser_sname() = ‘sa’)

BEGIN

PRINT ‘физическое удаление’

DELETE FROM dbo.Product FROM dbo.Product JOIN Deleted

ON Product.ProductID = Deleted.ProductID

END

ELSE

BEGIN

PRINT ‘логическое удаление’

UPDATE Product

SET IsDeleted = 1 FROM dbo.Product JOIN Deleted

ON Product.ProductID = Deleted.ProductID

END

Для тестирования триггера логического удаления мы выполним следующий запрос, который удаляет строки из таблицы Product. Следует особо отметить, что я зарегистрировался в системе как Noli\Paul:

DELETE Product

WHERE Code = 4053′

Получим следующий результат:

Логическое удаление

Теперь посмотрим на флаг логического удаления:

SELECT Code, IsDeleted FROM dbo.Product WHERE Code = 1053

Будет получен следующий результат:

Code      IsDeleted

1053     1

Подключившись заново как пользователь sa, я снова попытался выполнить ту же инструкцию DELETE:

DELETE Product

WHERE Code = ‘1053’

Результат будет следующий:

Физическое удаление (1 row(s) affected)

(1 row(s) affected)

Первая строка (1 row(s) affected) явилась результатом исходной инструкции DELETE. Несмотря на то что она была перехвачена триггером INSTEAD OF и исходное удаление игнорировалось, инструкция отрапортовала об успехе операции. Вторая строка (1 row(s) affected) стала результатом выполнения инструкции DELETE уже в триггере Product_LogicalDelete. На самом деле именно эта инструкция и удалила физически строку таблицы.

Восстановление логически удаленных строк

Прежде чем быть физически удаленной пользователем sa, строка может быть восстановлена установкой значения столбца IsDeleted в значение false. Если в базе данных реализован метод обеспечения безопасности данных на уровне строк, описанный ранее в этой главе, то триггер AFTER UPDATE может проверить наличие у пользователя административных привилегий на данную строку и восстановить значение столбца IsDeleted в 0.

Фильтрация логически удаленных строк

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

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

Каскадное логическое удаление

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

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

Реализация каскадного удаления во время чтения

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

Реализация каскадного удаления во время записи

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

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

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

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

Логическое удаление и ссылочная целостность

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

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

Маркировка неактивности

Системы, в которых реализован механизм логического удаления, часто имеют еще один уровень, определяющий бездействие строки, например флаг активности или устарелости данных. Эти флаги позволяют пользователю маркировать строки как недействительные без необходимости удаления данных. Например, в лаборатории R&D, занимающейся разработкой новых материалов, исследователям не хочется постоянно сталкиваться с тысячами устаревших версий формул. В то же время они не хотят удалять и сами существующие данные. Маркировка формулы как недействительной позволяет скрыть ее, однако в случае необходимости — извлечь.

Архивирование данных

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

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

Хранимая процедура может запросто реализовать вставку данных в архивную таблицу и удаление их из текущей. Приведем пример:

CREATE PROCEDURE pProduct_Archive (

@Code CHAR(15) )

AS

SET NoCount ON BEGIN TRANSACTION

INSERT Product_Archive SELECT *

FROM dbo.Product WHERE Code = @Code IF @@ERROR <> 0 BEGIN

ROLLBACK TRANSACTION RETURN

END

DELETE dbo.Product WHERE Code = @Code IF @@ERROR <> 0 BEGIN

ROLLBACK TRANSACTION END

COMMIT TRANSACTION RETURN

Хранимой процедуре, скорее всего, придется перемещать строки нескольких таблиц. К примеру, при архивировании заказов следует переместить строки таблицы [Order] и таблицы OrderDetail.

Резюме

В этой главе мы заставили Т-SQL решить множество сложных вопросов. Сложную обработку данных и поддержание правил бизнес-логики лучше всего реализовать с помощью серверного программного кода. Только когда правило реализовано на сервере, гарантируется его стопроцентное корректное выполнение. Реализацию правил за пределами сервера, скорее всего, можно рассматривать как исключение. Серверный программный код идеально подходит для процедур INSERT, UPDATE, DELETE и FETCH, сложных бизнес-правил, поддержания сложной ссылочной целостности, ведения журналов аудита данных и логического удаления.

В следующей главе мы продолжим изучение темы программирования на Т-SQL и рассмотрим решение еще более приближенных к практике задач.

11           

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии