Honestly, I've never done a database that *deleted* anything. Instead, records are usually marked inactive. I don't see how a stored proc is going to save you there though - except in one instance I remember some dba creating a delete proc that checked the rowcount from the delete and rolled back if it was greater than 1. Other trick occasionally used is to put a delete trigger on a table that copies the deleted records to a changes logging table. Revoking 'delete' on that table keeps things pretty safe - then you can always roll back.

Also, in practice using an object-relational mapping tool like cayenne - this never comes up since the developer never directly accesses the database - he edits the object graph and commits the changes.