Many of these ideas are great, but I don't think very harsh failures meshes very well with the way moods work now - meaning, how often success or failure is simply out of your control. Like the idiot weaver I've got standing on my barren, waterless mountain top right now screaming for shells, or anyone who has ever demanded glass on a map without sand.
On the other hand, it's lots of fun to have
some elements the player can't control or prepare for - semirandom invasions, foreign visits, weather, relationship dramas or other weird events would be awesome for this reason - but it's not fun to lose your whole fortress to a completely random occurance. Losing a dwarf is fine though.
Why not let the moody dwarfes aquire the ressources in other ways then from stockpiles ?
Just before going nova, they could check the fortress for substitutions ...
Urist McFeydwarf : My this steel floodgate is just what I need, surely nobody will miss it from this
remote tunnel, and it feels just so warm under my hands.
or
Bomrek Nobleguy : Has anybody seen my golden, gem encrusted cabinet ?
I *love* this idea!
A failed dwarf could make a dwarf realize he needs change in his life.
The results of this could be a change in personality, as already suggested. But perhaps it could mean that the dwarf might loose experience in the skill that was related to his failed artifact, this experience could then be partially transferred to some other skill.
Another cool thing would be if the dwarf would steal some armor, weapons, food, and other supplies, and then just leave your fortress in search of adventure and maybe something to give his life meaning again.
While I kinda like the idea that a failed mood should be invariably fatal (just one of those quirks of the dwarven mind) I like this idea as well. Perhaps different kinds of moods could have different results if they fail? I.e. fey moods might be fatal, possessions could lead to insanity, others might just lead to the dwarf becoming chronically depressed, but not necessarily suicidal... I dunno.