Я в основном рассматриваю триггеры, как костыли при модернизации уже готовой системы — например, добавить аудит изменений данных, апдейты полей в FK-таблицах при отклонениях от нормальной формы в угоду перформансу итд. Изначально на них закладываться я бы не стал, кроме случаев, когда например необходима железная защита от неадекватных действий слоя приложения критичных данных (скажем удаление финансовых транзакций определенного типа, которые ни при каких условиях не могут быть удалены). Всю логику на триггерах крайне желательно описывать и это описание прикладывать к БД и не забывать обновлять в репозитории.
Теперь насчет холивара насчет размещения бизнес-логики — в БД или в слое приложения? Как всегда, самый оптимальный с точки зрения производительности вариант — паллиативный. Он же самый дорогой и трудноподдерживаемый. Но по-другому не бывает — хотите выжать максимум из системы — используйте сложные структуры. Я стараюсь безусловно прятать логику в SP, если:
а) операция критична с точки зрения бизнес-целостности ключевых данных
б) операция производит или манипулирует большим количеством связанных данных и/или требует специфических опции БД (локи, частичные откаты, try/catch итд) для эффективного выполнения.
в) знания о бизнес-модели и процессах в проектируемой системе есть только у бакендщика, а фронтенд-разработчики — просто рисователи форм. Иногда бывает быстрее, собрав требования с заказчика, реализовать по ним бронебойный бакенд, чем рассказать остальным как это должно работать, а потом отлаживать всем миром все по функционалу и производительности.
В любом случае, логику, частично реализованную в БД, хорошо бы детально описывать и описание класть в репозиторий. Желательны и тестирующие логику скрипты.