Games that actually made me care enough to involve myself so much I actually tear up are few... but of the top of my head I get:
Lost Oddysey... every freaking part of the thousand years of memories... and one or two story parts that really punch your guts in.
Before that, the endings of persona 3 and 4.... oh and Gears of War 2, you know witch part (My best friend and I played both Gears Co-op, some of a tradition with co-op franchises is that we choose one of the characters and stick with it trough the whole franchise. Guess who I was playing)
before all that, the first game that made me cry out of sheer involvement and actually CARING about the plot, Final Fantasy 4.