I'll be honest: I laughed my ass off when Aeris died. I thought she was an extremely irritating character, and to the extent that I actually gave a shit about any of the characters (not much), I wanted Cloud to end up with Tifa.
FFVI -- When Celes tried to commit suicide. Her theme just fits the scene so perfectly, makes it a real tear-jerker. Also the whole thing with Locke and Rachel.
Chrono Trigger -- The ending. Watching Chrono and Marle drift off into the sky with that beautiful music playing, it really got to me.
Radical Dreamers -- Several of the endings, several parts within the story -- all of them accompanied by either the music box theme or Star-Stealing Girl. Both of those themes have always had a very powerful effect on me.
FFIII -- I started playing this right around the time Avien died. I heard Winds of Eternity before I actually got to the point in the game where that source plays. When I got a bit further in the game and heard the original, I definitely shed a tear or two.
LoZ: Twilight Princess, Xenogears, Shadow of the Collosus, Beyond Good and Evil, and Prince of Persa: Sands of Time came close at a couple of points, but I don't think I was ever actually in tears.
Interesting, now that I think of it: I have a much more powerful emotional response to the older console games than I do to those with more realistic graphics. I think it's because the older games leave the gamer's mind to fill in the details and give the characters life, which gets us far more attached to them.