Каскадный флаг мягкого удаления внешнего ключа через триггер обновления в SQL Server

Можно ли реализовать on update cascade с помощью триггеров? Я написал следующий пример кода (также на http://sqlfiddle.com/#!6/d7298/ 1):

create table Parent
(
    Id int not null,
    IsDeleted bit not null,
)

alter table Parent add constraint PPK primary key (Id, IsDeleted)

create table Child
(
    Id int not null,
    IsDeleted bit not null,

    ParentId int not null,
    ParentIsDeleted bit not null,
)

alter table Child add constraint CPK primary key (Id, IsDeleted)
alter table Child add constraint CFK foreign key (ParentId, ParentIsDeleted) references Parent(Id, IsDeleted)
go

create trigger ParentAfterUpdate on Parent
after update
as
begin
    if update(IsDeleted)
      update c set c.ParentIsDeleted = i.IsDeleted from Child c inner join Inserted i on c.ParentId = i.Id
end
go

insert into Parent values(100,0)

insert into Child values(1000,0,100,0)

update Parent set IsDeleted = 1

Это не работает, так как ограничение CFK вызывает ошибку. Я надеялся каскадировать обратимые удаления, чтобы дочерние записи удалялись при каждом обратимом удалении родительской записи.

Удаление IsDeleted из CFK может позволить мне каскадировать обновление до Child, но я считаю, что в высококонкурентной среде должно быть возможно получить поврежденное состояние базы данных:

в T0: Entity Framework загружает родителя в память. Родитель не удаляется.

в T1: родитель мягко удаляется другим независимым запросом

в T2: EF вставляет дочернюю запись, но, поскольку IsDeleted не является частью внешнего ключа, есть активный дочерний элемент, указывающий на удаленного родителя.


sql-server entity-framework triggers soft-delete cascade
person Mark13426    schedule 30.04.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

С реляционной точки зрения ваши PK недействительны, потому что существует их подмножество, которое само является допустимым PK (столбец Id). Это также позволит иметь две строки с одним и тем же идентификатором, но одну удаленную, а другую нет (я полагаю, это не то, что вам нужно). Если вы действительно хотите пойти по этому пути, я бы сделал PK только для столбца Id и, возможно, уникальный для Id, IsDeleted. Ваш FK может ссылаться на любой уникальный ключ, а не только на PK.

Кроме того, используйте параметр on update cascade при объявлении FK, он позаботится об обновлении столбца ParentIsDeleted, а затем вам понадобится триггер для распространения на «IsDeleted». См. код:

create table Parent
(
    Id int not null,
    IsDeleted bit not null,
)

alter table Parent add constraint PPK primary key (Id)
alter table Parent add constraint uq unique  (Id, IsDeleted)

create table Child
(
    Id int not null,
    IsDeleted bit not null,

    ParentId int not null,
    ParentIsDeleted bit not null,
)

alter table Child add constraint CPK primary key (Id, IsDeleted)
alter table Child add constraint CFK foreign key (ParentId, ParentIsDeleted) references Parent(Id, IsDeleted) on update cascade
go


create trigger trChildUpdate
on Child
after update
as
select trigger_nestlevel(object_id('trChildUpdate'), 'AFTER', 'DML');
if ((select trigger_nestlevel(object_id('trChildUpdate'), 'AFTER', 'DML')) > 1)
    return;
update c 
set c.IsDeleted = i.ParentIsDeleted 
from Child c inner join Inserted i on c.Id = i.Id
go


insert into Parent values(100,0)

insert into Child values(1000,0,100,0)

update Parent set IsDeleted = 1

select * from child
person dean    schedule 30.04.2014
comment
Спасибо. Я возьму подход с уникальным индексом. Можете ли вы прокомментировать, сколько места для хранения выделено для uq? Это 4 + 1 = 5 байтов на строку? Я считаю, что в случае некластеризованных индексов это размер ключа индекса в сочетании с размером кластеризованного индекса. Просто хочу знать, сколько дополнительного места необходимо для поддержания вышеуказанной структуры данных. - person Mark13426; 30.04.2014
comment
Очень небольшие накладные расходы, если таблица сгруппирована по Id. Вы правы, что NCI будет включать столбцы из CI, но не дважды. - person dean; 30.04.2014