Personally, the only time I delete something from a Stack Exchange site is when I asked myself, does this make the Internet better? and concluded no. Things like:
- read the OP's question wrong, leading to a silly (or plain "off-topic") answer
- was mistaken, misinformed, or just up too late, leading to a technically incorrect answer
- someone else posted an answer that covered everything my short answer did, but covered it far better, and expanded upon it. My answer is now just redundant.
- question was a silly mistake and is unlikely to be of any use to anyone else (e.g., left out a semicolon) (should be closed as "too localized", if not deleted)
- asked question on the wrong site, noticed before any answers (delete and post on correct site)
In every case, if the community disagrees with my assessment of value, my question or answer should be undeleted. When I posted it, I gave a license under CC-BY-SA, which is non-revocable, so legally its OK to undelete it. When I posted it, I published it. Not only did I publish it, I published it in a venue where I know that others may edit, answer, review, comment on, etc. it; I have given up control over it. Morally, I have no qualms.
The name Creative Commons is not a mistake. My published content is now part of the commons, and the community may (within the terms of CC-BY-SA) do with it as it pleases.
I feel this should be the normal, everyday handling of deleted questions. There are a few things, a very small minority, which need special handling such as when Stack Exchange receives DMCA notice, court order, or a notice under CC-BY §4(a). But those are exceptions. We have diamond mods and Stack Exchange employees to handle exceptions. The general policy should not be based on exceptions.
[Actually, if there were an easy way to give a CC-BY §4(a) notice, that is, request something no longer be attributed to you, I think that'd handle a lot of the objections to community undeletes.]