A lot of people always say it's Aeris in FFVII, and hey it IS an emotional moment for people who were playing it.
But something I STILL get in tears about to this day about FFVII is when Tifa remembers the day her mother died. That still breaks me.
There was also the Black Mage massacre in FFIX (just before fighting Black Waltz 3), I was playing that today and it still makes me bitterly tearful.
Oh, the final case of Phoenix Wright: Trials and Tribulations sent shivers of ZOMG down my spine. And for a very personal reason, the second case in Phoenix Wright: Ace Attorney (especially concerning the last piece of evidence)....I'm only going to say it was therapeutic.
And then there was Lavender Tower in Pokemon Red/Blue.
I dare any of you who played that to not get enraged at Team Rocket for that.