Very few games have done this, but I will admit, there are several that do. For instance, the only game where 90% of the people who playes it couldn't help but sniffle. Final Fantasy VII--the death of Aeris. I'll admit, I was so shocked it hit me like an 18 wheeler. At first I couldn't move when I started the fight with Jenova, but I soon unbuckled. Very emotional.
Final Fantasy X's ending movie gets me every time. I start hearing the final music and my heart starts to race. Its such a tear jerker its not even funny. Although it was partially nullified by X-2's ending movie, it still gets me no matter what. Its just, I had been playing for 60+ hrs with the character that I kinda grew attached to him, even felt like I was in the game watching, so it was powerful. But thats the way I am when I play--I get sucked in so much, I feel like I'm there.
The 3rd and final tearful game would deffinately be Metal Gear Solid 3. The best game ever conceived. No question. The final movie (which is 35 minutes +) is so powerfuly done I get goosebumps everytime I watch it. And the final two scenes with Big Boss at the Presidents office and at Arlington. I mean, you can tell he is in pain by just looking into his eye. He is a shattered man. Not only the scenes, but the powerful music captivates me too. My favorite game of all time is the most powerful game of all time.