I do that primarily through foreign key constraints, delete rules, and triggers. I like those because they are freakin' hard to circumvent - even for a dba. sp's on the other hand could be called - or not - by the dba.

Query, update, and insert are typically handled by OR-mapping software. The developers see an object model in a language they expect and a relatively simple paradigm - edit objects, call save at appropriate places. Each save call results in a transaction.

Just my practice/preference.