One of the most notable updates of the Sanity mechanic (which was something like mental hit points in older games) has been carried out by games such as Unknown Armies. UA sanity works in such a way that, while it can make you more used to seeing horrible things (becoming "hardened" in respect to some horror, like eviscerated bodies), it also makes you colder, insensitive and so on (more likely to carry out violent deeds), while in Call of Cthulhu you just kept losing your sanity points until you went crazy and had to be institutionalized. Delta Green partially adapted from Unkown Armies, you can "adapt" to violence, helplessness but never the Unnatural, as every encounter is a new feeling and your brain will never understand what's ununderstandable.
Dark Heresy has an insanity rating, that functions similarly. Players start at 0 insanity and go up from there. Insanity can be gained from psyker or demonic powers, failing a fear test, or if the DM just wants to give some to the player. There is usually a willpower test in order to avoid taking the insanity damage. Every 10 points of insanity, a player rolls a willpower test, and, should he fail it, the rolls on a Mental Trauma table that imposes a temporary penalty. Upon reaching 40, 60, and 80 insanity, the GM gives the players a mental disorder, such as a phobia, visions, nightmares, delusions, etc. At 100 insanity, a player is terminally insane and is removed from play (a houserule is to allow a character to burn fate to regain sanity). Gaining insanity is not a bad thing because if the first digit of your insanity total is double or more a thing's fear rating, you are immune to its petty horrors. Dark Heresy's Corruption mechanic works similarly but is a reflection of how tainted your character is by the Warp. Adeptus Evangelion inherits this Insanity system from Dark Heresy, and Fantasy Flight Games' other WH40K RPGs use it as well (with the exception of Black Crusade, since the average servant of Chaos is barking mad to begin with).
The system has also been stolen by clueless video game designers who fail to understand that it's hard to believe your character is looking at something that is making them go insane when you, the player, are looking at the same thing and it's just kinda funky looking. A notable exception to this rule is an obscure game called Eternal Darkness: Sanity's Requiem, which let you actually see the effects of your character's slipping grasp on reality as his sanity meter depleted. This could range from hallucinations (e.g. walls dripping blood, your limbs exploding when you attempt to cast a spell) to fourth-wall breaking effects (such as pretending to delete all your save data when trying to save the game or displaying a fake "controller unplugged" message as you enter a room full of zombies). The game "Call of Cthulhu: Dark corners of the Earth" also uses distortions of the player's view and experiences to convey insanity.
Sanity is used pretty well in Amnesia: The Dark Descent, mostly as a way of prolonging horror. Monsters are only encountered infrequently, and their rarity keeps their potential scare factor high. Staring at them will deplete sanity and cause the player's vision to swim and blur, making sure that the player can never get a good look at the monstrosity that is chasing them. This helps to keep them mysterious and get the player's mind filling in the blanks. Decreased sanity will also cause several changes in the environment, from discomforting insect/chewing noises in the background, to certain features such as paintings becoming more horrifying, to even phantom monsters appearing to terrorize the player further.
Fun Fact: Sanity is a legal term and not a clinical term.