Generally, I only like blood and gore when it's either realistic or in a situation in which all pretense of realism has been dropped. For example, I loved the violence in Prototype, since a mutant super-powered badass could understandably dish out that level of destruction. My favorite bit of Prototype ultraviolence was when I got the streetsweeper power for the tentacle arm, and used it in a densely packed crowd of people. Man...limbs went flying EVERYWHERE!
Another example of when it's alright is with No More Heroes. Violence is not portrayed realistically at all, so if Travis Touchdown can survive crap that would kill a terminator, it's actually less unrealistic that his bi-sected enemies should fill the screen with clouds of blood (and coins, but whatever).
However, an instance where it is not okay is when unrealistic violence is inserted into an otherwise fairly serious game. Fallout 3 is usually somewhat compliant with the laws of physics, but when using realistic weapons on realistic human enemies, a shot in the middle of the head should NOT decapitate a person and send their head flying off in three different directions. You know, a human head shot with a high-powered assault rifle might split open, sure, but it's not going to go flying. Human necks are made of bone and muscle, not whipped cream. It's even worse when this happens with a laser, you know, LIGHT, somehow imparting force equivalent to the kick of an NFL star.
