#sql-server
#sql-сервер
Вопрос:
У меня есть очень общий набор из трех таблиц, в которых хранятся все мои данные, который работает блестяще (здесь мы говорим о небольших объемах данных)
DataContainer — управляет «записями»
PK - DataContainerId
FK - ParentDataContainerId
FM - ModelEntityId
Поддержка данных — управляет управлением версиями
PK - DataInstanceId
FK - DataContainerId
IsCurrent [bit] NOT NULL CONSTRAINT [DF_DataInstance_IsCurrent] DEFAULT ((1)),
ModifiedBy [nvarchar](50) NOT NULL CONSTRAINT [DF_DataInstance_ModifiedBy] DEFAULT (suser_sname()),
ModifiedDateTime [datetime] NOT NULL CONSTRAINT [DF_DataInstance_ModifiedDateTime] DEFAULT (getdate()),
Значение данных
PK - DataValueId
FK - DataInstanceId
FK - ModelEntityId
ValueText --the actual values
Проблема: когда запись удаляется, мне нужно пометить все дочерние записи для удаления.
Попытка
--flag current record as deleted
update DataInstance
set IsCurrent = 0
Where DataContainerId = @DataContainerId
And (@ModelContainerId is null or @ModelContainerId = ModelContainerId)
--remove all child records
declare db_cursor for
select sc.DataContainerId as 'ChildDataContainerId' from DataInstance di
inner join datacontainer dc on dc.datacontainerId = di.datacontainerId
where parentdatacontainerId = @DataContainerId
declare @ChildDataContainerId int
open db_cursor
fetch next from db_cursor into @ChildDataContainerId
while @@fetch_status = 0
begin
exec dataInstance_Delete null, @ChildDataContainerId --current sp
end
close db_cursor
deallocate db_cursor
Проблема в том, что я не могу использовать курсоры рекурсивно (поскольку я получаю сообщение об ошибке, что курсор уже открыт), поэтому этот SP будет работать только на один уровень глубже.
Есть ли более хитрый способ сделать это?
Комментарии:
1. Хранение всех ваших данных в одной таблице и последующее использование курсоров для обработки ваших записей — это практика, от которой вам следует отказаться. Вы напрашиваетесь на головную боль, когда игнорируете разумность нормализации ваших данных и мощь заданной логики, для которой был разработан SQL… Может быть, вам следует рассмотреть возможность рефакторинга для вашей собственной выгоды позже?
2. Поскольку я ненавижу «палки в колеса», которые просто оставляют негативные замечания, а затем уходят, вот одно из решений вашей проблемы, если вы решите двигаться вперед: используйте язык прикладного программирования за пределами SQL для выполнения рекурсии. Обычно они не ограничены правилами рекурсивных вызовов, и вы можете проще обрабатывать записи с помощью набора записей или эквивалента, если предпочитаете логику строк. Удачного кодирования…
3. Я должен согласиться с Tahbaza, но если вы настаиваете на этом маршруте, я бы использовал триггер. Триггеры могут выполняться рекурсивно (до определенного (настраиваемого, я полагаю) уровня).
4. Не могли бы вы объяснить структуру вашей таблицы. В вашем сообщении говорится, что у вас есть только одна таблица, но когда я смотрю на хранимую процедуру, которую вы перечислили, похоже, что вы ссылаетесь на две таблицы, таблицу с именем DataInstance и таблицу с именем datacontainer .
5. Я больше нигде не использую курсоры (использую set logic), по сути, у меня есть OO-дизайн базы данных в реляционной базе данных. Я действительно хочу ссылочную целостность для всех моих метаданных, но для самих данных, очевидно, их очень мало. Это означает, что я могу использовать одну схему для каждого приложения, которое я разрабатываю .. это сработало очень хорошо. Возможно, вариант NoSQL или XML подошел бы вполне, но мне нравятся базы данных в целом.
Ответ №1:
Как предположил Тахбаза, это был довольно простой триггер,
create trigger DataInstance_Trigger
On DataInstance
After update
as
Begin
Update
DataInstance
Set
DataInstance.IsCurrent = 0
From DataInstance di, Inserted i
Inner join DataContainer dc on
i.DataContainerId = dc.ParentDataContainerId
Where di.DataContainerId = dc.DataContainerId
di.IsCurrent = 1
Завершение