There have actually been a lot of games that have made me cry over they years. Some of the ones I remember are:
Metal Gear Solid 1, 2, 3 and Peace Walker
Kingdom Hearts 1, 2, BBS, Re:Coded and 358/2 days
Final Fantasy 10 and (obviously) Crisis Core
Castlevania Order of Ecclesia
I'm pretty sure there are more but I can't really remember any others. I've kind of always been very sensitive to emotional things, so a lot of games get at least a tear or two but the ones I mentioned were games that I remember being wrecked after playing.