My most notable death is unremarkable in its physical details, but is nonetheless a tale of pure Shakespearean woe.
Imagine a happy dwarven family. Father in the militia, mother is a blacksmith. Two small children, one an infant
Someone is careless hauling a goblin prisoner and he gets loose in the commons. A long chase, made difficult by the openness of my fortress layout. I send in the army, but because they're green recruits in bone armour some are ravaged and the rest are called back, though none dies. The goblin runs into a long hall, stabs some passers-by to death but is subsequently caught in an ad hoc cage trap. It just so happens that one of those passers-by was the elder son of the above-mentioned blacksmith.
Well, things aren't going so well for her and she becomes more upset by the day. A moment comes when she snaps, and snaps hard. She goes berserk and murders her infant, her only surviving child. Naturally I send in the militia but as a result of the goblin fight only two are able to respond quickly and the first one on the scene is her husband, who then has to put her down. And all because he couldn't stop that goblin.
And what happened to the soldier? Did such a twisted fate leave him longing for death or madness? I would love to say it did, but he had had a pretty decent drink lately, admired a sublime chair and perhaps even made a friend. So he got over it to live another day, battle another time, and die a different senseless death or other. And the world went on.
That incident was really what clued me in to the depth this game can offer, though that one time I made a fort of 150 dwarves suspended over a gorge and cut it all loose was pretty fun too.