none
Удаление(обновление) из связанных таблиц RRS feed

  • Общие обсуждения

  • Добрый день всем.

    Хочу обсудить проектирование архитектуры базы данных, где необходимо удалять (обновлять) данные из связанных таблиц (стандартная ситуация, навеяно этой темой Как работают триггеры в MS SQL? )

    Я всю жизнь реализовывал это через встроенное каскадное удаление, но у каскадного удаления есть один серьёзный недостаток - если таблицы связаны циклично (а это бывает очень часто и реализация другой модели БД представляет собой на порядок более сложную и алогичную модель БД), то я удалял то, что через каскадное не удалялось, просто из приложения. Понимая, что этот подход неправильный (потому что, на мой взгляд, БД должна быть "самодостаточна", все операции с её данными должны работать корректно независимо оттого, удаляются данные напрямую или через какие-то приложения), решил реализовать это через триггеры. Но в теме, указанной по ссылке выше, мне сказали, что бизнес-логики в триггерах быть не должно. Как же тогда реализовывать каскадное удаление данных?


    15 октября 2015 г. 9:22

Все ответы

  • Добрый день.

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

    15 октября 2015 г. 9:36
    Отвечающий
  • Да, и во многих случаях правильнее использовать не физическое, а логическое удаление сущности. Т.е. сущность из БД не удаляется, а только помечается как удаленная и в определенных местах интерфейса не показывается. Например, звонок принимал сотрудник А, мы его логически удалали, но вот его звонки все остались, и в них даже видно, что принимал их А. Но вот для нового звонка указать А уже нельзя.
    15 октября 2015 г. 9:37
    Отвечающий
  • Да, и во многих случаях правильнее использовать не физическое, а логическое удаление сущности. Т.е. сущность из БД не удаляется, а только помечается как удаленная и в определенных местах интерфейса не показывается. Например, звонок принимал сотрудник А, мы его логически удалали, но вот его звонки все остались, и в них даже видно, что принимал их А. Но вот для нового звонка указать А уже нельзя.

    и всё же, как быть именно с удалением и почему бизнес-логика в триггере - это неправильно?

    15 октября 2015 г. 9:46
  • Я же вам в пером случае написал: "для каждого случая отдельно". В триггирах это у вас ядерная бомба. Неправильный запрос и все, привет всем данным. Только в бизнес-логике приложения (сервиса).
    15 октября 2015 г. 9:49
    Отвечающий
  • В первую очередь потому что триггеры работают независимо друг от друга. У вас нет единого контекста происходящего при каскаде вложенных срабатываний, да ещё и с циклами. 
    15 октября 2015 г. 10:06
  • Я же вам в пером случае написал: "для каждого случая отдельно". В триггирах это у вас ядерная бомба. Неправильный запрос и все, привет всем данным. Только в бизнес-логике приложения (сервиса).

    Это как в бизнес-логике приложения? Получается, что БД спроектирована так, что удаление данных из одной таблицы напрямую нарушит целостность данных во всей БД? А если оператор БД не знает об этих всех особенностях (он и не обязан знать) и ему нужно удалить какую-то запись. Он вполне разумно будет предполагать, что удаление записи повлечёт за собой удаление всех связанных данных. Или создаётся ещё одно приложение, используемое эту же БД и создатели этого приложения должны учесть все эти нюансы. Это усложнение взаимосвязей между компонентами всего программного продукта потенциально влечёт за собой множество ошибок. Мне кажется, что корректность работы БД не должна зависеть от сторонних приложений и операции, касающиеся БД, должны быть инкапсулированы в самой БД.

    Я не прав?

    15 октября 2015 г. 16:41
  • В первую очередь потому что триггеры работают независимо друг от друга. У вас нет единого контекста происходящего при каскаде вложенных срабатываний, да ещё и с циклами. 
    А чем плохо, что один триггер не зависит от другого? Наоборот, это позволяет не задумываться о поведении другого триггера при внесении изменений в первый. Первый удаляет "первый уровень" данных и вызывает триггеры, на которых уже лежит удаление "второго уровня"
    15 октября 2015 г. 16:44
  • А чем плохо, что один триггер не зависит от другого?
    Коллега, я и Роман ответили вам уже. Тем, что жизненный цикл ПО не ограничивается только разработкой. У вас будет ситуация как в той старой байке, что если бы программисты строили города, то первый же дятел разрушил бы цивилизацию. Очень высока стоимость ошибки. Еще раз. Операция удаления, это очень опасная штука. Ее надо ограничивать, а не поощрять.
    16 октября 2015 г. 6:04
    Отвечающий
  • Инкапсуляция не равно "реализовать логику в триггерах". Можно реализовать её в хранимых процедурах, да ещё и на CLR написанных. 
    Можно сделать на триггерах. Но для этого нужен высокий уровень владения этим опасным инструментом. Нужно понимать, что вы делаете с рекурсивными вызовами (особенно при косвенной рекурсии в чуть более сложных схемах). Нужно понимать, как поведёт себя конструкция MERGE. Нужно понимать, как обрабатывать update в таблицах с parent-child иерархиями или более сложными зависимостями. И многое, многое другое

    PS. Вы поверьте, я по этим граблям ходил.

    • Изменено Roman Sergeev 16 октября 2015 г. 10:33
    16 октября 2015 г. 10:30
  • Для таких наивных ситуаций прекрасно годится каскадное удаление. В остальных случаях задумываться приходится
    16 октября 2015 г. 10:32
  • Инкапсуляция не равно "реализовать логику в триггерах". Можно реализовать её в хранимых процедурах, да ещё и на CLR написанных. 
    Можно сделать на триггерах. Но для этого нужен высокий уровень владения этим опасным инструментом. Нужно понимать, что вы делаете с рекурсивными вызовами (особенно при косвенной рекурсии в чуть более сложных схемах). Нужно понимать, как поведёт себя конструкция MERGE. Нужно понимать, как обрабатывать update в таблицах с parent-child иерархиями или более сложными зависимостями. И многое, многое другое

    PS. Вы поверьте, я по этим граблям ходил.

    А хранимые процедуры вызывать в триггере или как? Смотрите, вот следующая ситуация - появляется новый оператор БД и удаляет запись в БД операцией delete. И этот человек (или новый разработчик, который не знает тонкостей удаления записей из данной таблицы и должен создать приложение, работающее с данной БД) естественно ожидает, что операция delete обработается корректно, что после удаления записи БД не будет содержать некорректных данных в других таблицах.

    Я согласен с тем, что удаление - довольно рискованная ситуация, но разве некорректное удаление чем-то гораздо лучше случайного удаления? Случайное хоть сразу будет заметно, а некорректные данные в БД - мина замедленного действия

    17 октября 2015 г. 16:26
  • Для таких наивных ситуаций прекрасно годится каскадное удаление. В остальных случаях задумываться приходится

    Простейшая ситуация:

    таблица Users:

    - ID (PK)

    - FirstName

    ....

    таблица Friends

    - UserID_1 (FK to Users)

    - UserID_2 (FK to Users)

    каскадное работать не будет, скажет мол циклическое удаление. Надо реализовывать какой-то механизм. Как бы Вы сделали? В самом приложении, которое использует БД? Через триггер? Через хранимую процедуру (если так, то как её вызывать)?

    17 октября 2015 г. 16:34
  • Я согласен с тем, что удаление - довольно рискованная ситуация, но разве некорректное удаление чем-то гораздо лучше случайного удаления? Случайное хоть сразу будет заметно, а некорректные данные в БД - мина замедленного действия
    Нет, он получит сообщение, что удаление не возможно из-за внешнего ключа. И начнет разбираться, можно это удалять или нет.
     Как бы Вы сделали? В самом приложении, которое использует БД? Через триггер? Через хранимую процедуру (если так, то как её вызывать)?

    Конкретно для этого случая, я бы подумал о логическом удалении. Уж очень много в вашей базе будет завязано на юзера. И удаление, это потерять все то что вы по нему имели. Например, другой пользователь писал ему что-то важно и захочет найти, а нет, все удалено...

    17 октября 2015 г. 17:54
    Отвечающий
  • Нет, он получит сообщение, что удаление не возможно из-за внешнего ключа. И начнет разбираться, можно это удалять или нет.

    так всё же, реализовали бы удаление в клиентском приложении, использующем БД?


    Конкретно для этого случая, я бы подумал о логическом удалении. Уж очень много в вашей базе будет завязано на юзера. И удаление, это потерять все то что вы по нему имели. Например, другой пользователь писал ему что-то важно и захочет найти, а нет, все удалено...

    логическое удаление конечно хорошо, но значительно усложняет БД (и пользовательские приложения) и требует множества дополнительных действий. Кроме того, резко теряется наглядность. Например, есть пользователь, у него профиль в ASP.NET Identity и куча зависимостей на другие таблицы. Теперь пользователь удаляет свой аккаунт. Помечаем его как неактивный. Потом пользователь хочет зарегистрироваться вновь (с тем же емаилом) и хочет, чтобы его профиль был начат с чистого листа! Тогда что, всё удалять перед пересозданием? И таких нюансов тысячи, включая банальные потенциальные ошибки когда разработчик забывает откорректировать запрос с учётом выбирать только активных пользователей (или проекты только активных пользователей и т.д.). С логическим удалением - это уже совсем другой уровень сложности приложения, на который резонно может не быть бюджета (например, заказчик НЕ хочет, чтобы оставались какие-то упоминания об удалённом пользователе, в том числе переписка с другими пользователями и т.д.)

    17 октября 2015 г. 19:09
  • Вы все эти проблемы хотите решить переносом логики в плохо написанные триггеры? :)
    Вы описываете совершенно классические проблемы, вызванные плохим сбором требований и отсутствием хорошего QA. Конечно же, всё это требует бюджета. Но если его нет, то не стоит и начинать, как по мне.
    18 октября 2015 г. 6:48
  • Вы все эти проблемы хотите решить переносом логики в плохо написанные триггеры? :)
    Вы описываете совершенно классические проблемы, вызванные плохим сбором требований и отсутствием хорошего QA. Конечно же, всё это требует бюджета. Но если его нет, то не стоит и начинать, как по мне.

    То, что у меня триггеры плохо написаны - это другой вопрос, я в них можно сказать "новичок" и поэтому хочу разобраться. Но прежде всего я хочу разобраться, как наиболее правильно решить вышеуказанную проблему. Допустим, требования ТЗ говорят, что записи должны удаляться физически (такое требование заказчика, забудем про логическое удаление). Допустим, есть профиль пользователя (в том же ASP.NET Identity, не столь важно), есть десятки связанных таблиц (напрямую и косвенно), включая циклические зависимости. Как наиболее правильно физически удалить записи? Если через триггер нежелательно, каскадное удаление тут не поможет, то получается лучше всего через клиентское приложение? Только теперь вылазит ещё одна сложность - может оказаться так, что операция удаления будет проходить успешно при удалении не всех данных. В нашем приложении мы это учли и вообще не догадываемся о потенциальной проблеме. Но в новом приложении, написанном новым разработчиком, это может выпасть из виду. Если появится десяток приложений - потом попробуй разберись, какое из приложений портит базу. Поэтому меня всё равно тянет к тому, что БД должна быть "самодостаточной". Что если что-то идёт не так в самой БД - то проблему нужно искать в архитектуре БД и что никакой "внешний оператор" не может испортить БД.
    18 октября 2015 г. 7:18
  • Тут видите какая штука. В разных ситуациях нужны разные подходы. Я вот писал системы автоматизации в банке, сейчас пишу в спец операторе связи. И я в своих системах не использовал и не буду использовать ни каскадное удаление, ни триггеры, которые автоматом чистят смежные таблицы. Пусть чертыхаются программисты, администраторы БД, но для меня данные все. Я допускаю, что есть предметные области, в которых на данные которые вносил пользователь наплевать. Ок, в этих случаях каскадные удаления, удаления на триггерах вполне имеют право на жизнь.
    19 октября 2015 г. 6:42
    Отвечающий
  • Вам нужен application server. Вы можете сделать его выделенным сервисом. Можете использовать в его качестве сам mssql. Но не давайте клиентскому приложению непосредственного доступа к изменению БД. Сделайте API. Через хранимые процедуры, REST, что-то ещё, но не давайте ненадёжному клиенту ничего менять в своей базе
    19 октября 2015 г. 8:14
  • Тут видите какая штука. В разных ситуациях нужны разные подходы. Я вот писал системы автоматизации в банке, сейчас пишу в спец операторе связи. И я в своих системах не использовал и не буду использовать ни каскадное удаление, ни триггеры, которые автоматом чистят смежные таблицы. Пусть чертыхаются программисты, администраторы БД, но для меня данные все. Я допускаю, что есть предметные области, в которых на данные которые вносил пользователь наплевать. Ок, в этих случаях каскадные удаления, удаления на триггерах вполне имеют право на жизнь.

    понятное дело, что если физическое удаление данных неприемлимо - тогда проблемы с удалением как таковой нет (а есть другие проблемы, а именно значительное усложнение БД и бизнес-логики. Вот так навскидку - как те же уникальные индексы создавать? Например, название какой-то сущности должно быть уникальным. Если мы вешаем ключ на 2 поля (на название и на булево значение активен/неактивен) - то возникает ситуация, что не может быть 2 записи с таким же названием для неактивных сущностей, что уже нарушает бизнес-логику. Как быть теперь? Создавать триггер на insert и там проверять есть ли такое название только в активных? ).

    Давайте рассмотрим ситуацию, когда при пересоздании профиля (человек удаляет его и создаёт заново) - профиль должен быть пуст (такое бизнес-правило). Т.е. все данные из старого профиля должны быть удалены. Как бы Вы спроектировали это?

    19 октября 2015 г. 8:55
  • Вам нужен application server. Вы можете сделать его выделенным сервисом. Можете использовать в его качестве сам mssql. Но не давайте клиентскому приложению непосредственного доступа к изменению БД. Сделайте API. Через хранимые процедуры, REST, что-то ещё, но не давайте ненадёжному клиенту ничего менять в своей базе

    Итого мы приходим к тому, что обеспечение целостности данных на уровне самого mssql - очень хорошее решение :)

    Хорошо, как быть через хранимые процедуры? Вызывать их напрямую из приложения? Чем тогда триггеры хуже, если в них будет инкапсулирована та же логика?

    19 октября 2015 г. 8:59
  • Давайте рассмотрим ситуацию, когда при пересоздании профиля (человек удаляет его и создаёт заново) - профиль должен быть пуст (такое бизнес-правило). Т.е. все данные из старого профиля должны быть удалены. Как бы Вы спроектировали это?

    Мы обычно делаем признаком логического удаления поле DateTime с возможностью принимать значение null. Если null, то это активная запись. Если не null, то запись неактивна, а в поле дата-время удаления. Для обеспечение логической целосности добавляете у таблицы констрейн проверяющий что нет записей с совпадающем именем и null в поле с признаком логического удаления.

    Чем тогда триггеры хуже, если в них будет инкапсулирована та же логика?

    Не будет. Т.к. триггер работает только с этой таблицей и не знает, что происходит при удалении из других. А вот в хранимке вы напишете "аккуратное" удаление, которое почистит только то что нужно. Например, у пользователя с которым переписывался удаляемый все сообщения остануться, пропадет только их адресат/автор.
    19 октября 2015 г. 9:34
    Отвечающий
  • Любое решение хорошее, если оно доведено до конца. Процедуры в тысячу раз лучше тем, что вы управляете потоком выполнения. Это: а) надёжность и устойчивость, б) зачастую ещё и производительность

    Физическое удаление в свете, к примеру, законов о защите ПД, безусловно, может потребоваться. Надо только понимать, что всё это требует продуманного решения. Чтобы резервные копии не подводили под статью и пр. Нужно очень чётко изолировать соответствующий домен на всех уровнях.


    • Изменено Roman Sergeev 19 октября 2015 г. 10:53
    19 октября 2015 г. 10:52