Red Dead Redemption.
The cougars man... the freaking cougars. Out of nowhere you hear their howl, their roar and then your horse... oh how the poor thing screams with its last breath. Then you're on the same ground man, the same ground! You look to your right, your left. It attacks! It's toying with you though, it ran to the other bushes... you keep your sights trained on it, you pull of some shots, you hear it cry out in pain and you chase the fecker down.
With the final shot you breathe a sweet sigh of relief... what's that?! Another roar?! You turn immediately around enter Dead Eye and take your shot. A final shout as it falls down. You then proceed to skin your trophies and move on. You also jack the next guy's horse, but that's not a nice thing to do.
Well, the first time it startled me as I had no idea what the heck just hit me. I really hate those things man, now I hunt them even if they don't hunt me as I drive those little fecks to extinction. There is little else to do when you hate something that much. The wolves have earned my ire as well, I will get them too -- in due time.
Horror games though? Hmm... nope. I seem to see right through those as I expect it. I can't say there was a single moment that ever actually got me, that wasn't scripted anyways and even then those are pretty rare.