The general way I tend to work with character death (in the system games I run) is that it's something that can happen if the dice hate you enough, or as part of your character's story. In general, death in my games is a form of apeothesis, not of losing. An example would be the story of two characters in a Vampire game of mine, Mark the Crazy Bastard, and Barkikus Andronicus.
Mark the Crazy Bastard had something go wrong from the moment he became a vampire. His mind shattered into dozens of fragments, and he began to hear the voices of those he had consumed the blood of in his head. For 100 years, he haunted the city of Rome, making many enemies, and eventually being present when a powerful elder demon was unleashed through a hellgate. Possessed by the demon, the crazy bastard's legs were cut off by his own companions, and he futily expended all of his supernatural power to keep on crawling. The demon made its way to the hearth of Rome, and thanks to the timely sacrifice of a few mad vestal virgins, created a body for itself, a reborn clone of Romulus to spring from the hearth of Rome and damn humanity. But it was not to be, not if Mark the Crazy Bastard had anything to say about it. The critically injured vampire thrust himself into the flames of the hearth of Rome, and drank the blood and consumed the soul of Romulus reborn, ascending into the heavens on a pillar of fire.
Out of game, of course, I made it quite clear to everyone around the table that the character's actions were suicidal, but that they would make him a big damn hero. (I also allowed the character to bequeath his derangements upon any character in the campaign when he was dead. His rivals become incredibly destructively maddened.)
The case of Barkikus is different. That particular case was of a character so incredibly changed by an action in game that he became unplayable afterwards. A Vampire character's sanity and morality is measured by the humanity stat. To put it simply, Barkikus had been hovering at humanity 1 for several sessions, as he regularly slaughtered dozens of men, and had once drunk the blood of 522 random people around the prisons and garrison of Jerusalem. The breaking point for Barkikus finally came when he set out to slaughter everyone in a tent, only to find that it was full of women and children. Not wanting to control his rage, Barkikus hurled the women and children across the desert regardless, hoping merely to wound, instead of to kill. In this particular act of cruelty, the beast within him finally reared up, and fought him within his mind. Barkikus refused to give up the fight, and slaughtered his own beast, as the shadow within him smiled, knowing it had condemned him to eternal agony through death. Barkikus ended the battle in his mind with humanity 10, the absolute maximum, and immunity to many of the weaknesses of a vampire. But his killer instinct was lost. All of the rage that had driven him was gone.
The character retired and wandered into the desert, and was never seen again until 600 AD, where he was spotted in a cave in Saudi Arabia...
The main thing with character death is: Make it meaningful, Make it dramatic. Dying in the first ten minutes of the story to establish how badass the villains are is for mooks and characters who failed to renew their contracts, not major PCs. (At least in a typical ongoing game)