Если БД полностью нормализована, то совсем можно обойтись и без ХП и триггеров, пользуясь только заданием ключей, check constraint'ов и отношений ( или все-таки нет ? ). На практике так не делается. Во-первых, полностью нормализованная система очень трудозатратна. Во-вторых, она будет не гибкой, а достижение некоторой гибкости сделает систему сверхсложной. И в программировании, и в эксплуатации. Да и вообще, вряд ли физически возможно создать мало мальски сложную и при этом полностью нормализованную систему. Я уж не говорю о том, что 1.невозможно будет найти достаточно людей для ее поддержки, а также о том, что 2. не представляю, появится ли в ближайшее время ЭВМ, у которой ресурсов будет достаточно для того, чтобы такая система на ней заворочалась.

(Я подразумеваю также, что любое действие над такой БД должно быть корректно обработано или отвергнуто по той причине, что: 1.нарушено ограничение ключа, 2.значение не прошло проверку на соответствие условию или 3.данные нарушают ограничения, наложенные на связи между таблицами. А так - очень даже можно создать систему, которая будет работать только до тех пор, пока человек совершает над ней корректные действия).

Есть такой термин: бизнес-логика. Разработчики стараются отделить мух (бизнес-логику) от котлет (костяка, физической структуры БД), помещая мух, например, в ХП, где они и живут (ну, не только в ХП, но ХП предпочтительней других мест, потому что неотделимо от БАЗЫ).
Другой случай, когда ХП полезны - объединение множества операций над данными в одном логически завершенном блоке. Могут возразить: а зачем в ХП - можно и из приложения выполнить ту же последовательность операций? А вот затем, чтобы из всех приложений вызывать одну и ту же ХП. А то захотят приладить наряду с WinForms-интерфейсом также Web-интерфейс, и чего-нибудь там напутают, недоделают... Могут также возразить: что же это за система, которая требует множественных изменений в данных? А вот такие они, реальные системы. В отличие от идеальных (см. выше, про нормализацию).

Примеров не будет. Потому что, действительно, все можно сделать и без ХП и триггеров.