INSTEAD OF Триггеры и пути CASCADE

Скажем, у меня есть 3 таблицы в иерархии:

TableA -> TableB -> TableC

TableC имеет отношение внешнего ключа с TableB, а TableB имеет отношение внешнего ключа с TableA.

Если я удалю запись в TableA, она должна каскадировать удаление вниз по иерархии. Использование ON DELETE CASCADE будет работать нормально.

Однако скажем, мне нужно поставить триггер INSTEAD OF на TableC. Мое понимание заключается в том, что триггер INSTEAD OF не может быть помещен в таблицу с каскадом удаления, идущим к нему. Взято из MSDN:

Для триггеров INSTEAD OF параметр DELETE не разрешен для таблиц, которые имеют ссылочное отношение, указывающее каскадное действие ON DELETE.

Если мне нужно удалить каскад delete TableB->TableC, мне нужно использовать триггер INSTEAD OF для принудительного выполнения ссылочной целостности, а затем у меня такая же проблема с TableB->TableA. Это простой пример, но представьте, что путь каскада намного больше. Похоже, он мог легко заснуть на протяжении длинного каскадного пути.

Итак, каковы наилучшие методы решения этого сценария?

Ответ 1

Предполагая, что вы должны использовать триггеры INSTEAD OF, а триггеры AFTER не являются опцией, лучший подход заключается в том, чтобы: a) жестко контролировать схему, чтобы вы могли b) script, чтобы INSTEAD OF запускался обычным образом для реализации CASCADE DELETE и любые другие операции, которые вам нужны.

Создайте ограничения FK, как и раньше, но без каскадного поведения. В имени FK используйте какое-либо соглашение, чтобы указать, какое поведение каскада и пользовательское поведение должно произойти, например:

FK_UC_DC_Table1_Table2 - обновить каскад, удалить каскад
FK_UC_DN_Table1_Table3 - обновить каскад, удалить set null

Используйте все, что имеет смысл, но создайте FK, они являются полезными метаданными для генерации кода, и вы можете использовать имена FK для записи директив для генератора кода.

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

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

Ответ 2

Стандартная передовая практика заключается в определении триггеров INSTEAD OF на представлениях, а не на таблицах.

Если вам нужно использовать триггер для обновления/удаления FK, лучше всего использовать AFTER, так как он всегда будет выполняться.

Если вы хотите отменить каскадные действия, но сохраните FK, просто установите для действия FK значение NO ACTION.