Call of Duty: Black Ops
A bunch of people on other forums have confirmed that it is actively scanning single player executables so I think using the trainer is a pretty big risk right now.
A suggestion would be to wait about a week or two and see if any Cheathappens members get VAC banned or not to determine Valve's policy on singleplayer cheating. (We don't want a repeat of Starcraft 2 :/)
Valve also gives warnings instead of flat out bans you. You can also contest their bans and they'll listen. At any rate I would still keep caution. There's always some risk given they provide SP achievements.
I however believe it's enabled, but not active because even in SP you're still "online", see Zombie Mode with online play, etc...
Yeah, mine is set to "0" in dvars but the default value is "1". I kinda doubt that'll disable VAC though.
There has never been a Single Player ban via Steam and probably will not be. If this one is using VAC2 for Single Player, then VALVe is definetly going to be having many lawsuits.