How about Defcon?
*relaxing music*
*pretty wireframes*
*dawning realisation that you've just nuked half a billion people*
Teph

System Shock
"How can you challege a perfect, immortal mashine?"
Yes, this! :D
System Shock 2
"I'm SHODAN"
Planescape Torment
"What can change nature of a man?"
Final Fantasy VII: Crisis Core
"We're friends, right?"
of course, Final Fantasy VII itself
Final Fantasy VIII
"Whatever" :D
Morrowind
"Wtf?! How big is this place?!"
Prince of Persia
"You cannot change your fate. No man can."
... And list goes on :D

yeah. I always laugh when i win.

Destroying the tower of Dagoth Ur and then emerging from his lair to find Morrowind cleansed from the blight was pretty incredible.

Damn, most of my personal emotional climax have been already quoted:
-Planescape: Torment as a whole, all the story irradiates emotion.
-The "true" end of S.T.A.L.K.E.R, I overwhelmed with relief for the marked one.
-The Helios ending of Deus Ex, where it seems that everything you have done was leading to this solution only.
-Dreamfall - I have never seen female characters so well written.
-Fatality in Prince of Persia trilogy
-Soul reaver 2 and Defiance ending made me shed a tear.

Lost Odyssey is the only game that made me almost cry. Especially the scenes with Lirum.

I've just checked that video (and a few of the other related ones) and this game looks awesome!!! I must've been sleeping when this came out to have missed it... Definately getting this :D

The third mission of Homeworld. You rush back from what should have been a routine testing of the hyperspace core (it went wrong, the ship you were meant to meet had been blown apart and then its attackers came after you) to find your home in flames. Brilliant acting and Agnus Dei, it still chokes me up.
And the choice you're forced to make on Virmire in Mass Effect.

OMG! Yes, I remember that scene - most serene scene in any game and so emotional!
Thanks for reminding me too!

How dare you compare The longest Journey with Dreamfall!!!!
The first was the original Wizard of Oz, the second - that Motown version! One a true adventure GAME, the next an interactive story that has less gameplay than any other game ever made - in fact it is not a game art all in my book!
I LOVE The Longest Journey, was a HUGE fan of The Longest Journey and any true TLJ fan will tell you what a travesty Dreamfall was on so many levels! The treatment of April Ryan was one of them! No wonder Dreamfall totally bombed! Can you imagine Lara Croft being a bit player in Tomb Raider 2, or Gordon Freeman a bit player in Half Life 2?! It was commercial suicide for FunCom and a travesty for TLJ/April Ryan lovers!
Rant over - sorry!

From newer games, i gotta say Lost Odyssey had the biggest emotional impact on me.
And Bioshock gave me the biggest "Oh ....My.....God......" moment.
Of the older ones, FF7 had the biggest WTF moment (end of disc 1),
FF8 just blew my mind, with graphics, dialogue, characters, and especially the music.
I will remember the music, for as long as i live.
The Fallout games were wonderful, gerat endings, and the option to just kill everyone you meet, was fantastic.
Resident Evil 1 and 2 scared the heck out of me.
Monkey Island, Full Throttle, Day of the tentacle, Sam & Max for humour.
And the list just goes on and on and on.
I've been more emotionally impressed by games, than by movies. ;)
[EDIT]: Forgot to mention Mass Effect. A game so great, that even my girlfriend have played through it twice.
(She even wants to change her last name to Sheperd)

Chrono Trigger. There were so many moments in that game that would make me laugh or smile, and several that were sad enough to make some cry.
The same applies to Final Fantasy VI.
Also, the cutscenes in the C&C series are absolutely freaking hilarious.

I have had lots of moments of surprise and shock in games. However, my most memorable moment is one that may have saved my home and life. It was Thief the dark project. I was (and still am) mainly an RPG player. I never much cared for "shooter" games. But thief was a perfect combination of stealth play with RPG atmosphere. Probably like so many other players, my goal was to get through a mission without a single kill or detection. So one night I was playing way too late. The lights were off and I was completely in the moment. My hearing was completely tuned to the game trying to distinguish any noise that might give guards away or give warning of their approach. I was so "in the game" that I was even being soft on the keys and mouse to kill all noise. I slowly became aware of a very soft "bzzt....bzzt...bzzt" noise coming from somewhere. It took a few moments to realize it wasn't the game. I finally figured out it was coming from the circuit breaker box which is in the same room with the computer. I opened the panel and sure enough one of the breakers was arcing. I could see the sparks in the dark. After replacing it I could see the black burn marks that had been hidden behind it. It's probably something that I would NEVER have noticed if my senses hadn't been heightened by playing Thief.

Brilliant, I'll have a look at that later on!
thanks man :D