No the Pi doesn't turn the screen off it actually blanks the screen, but technically there is still a video signal.
You can disable the composite blanking / screen saver though - http://www.raspberrypi.org/phpBB3/viewt ... 75&t=67647
As for a detection circuit feel free to play around with my old design:
Admittedly the number of transistors was probably overkill but nevertheless it worked.
The video signal is fed into a 2 transistor stage (Tr1 + Tr2) to amplify the video buzzing tone, this is then rectified (AC to DC) using diodes D2 and D3 which feeds the capacitor.
When video signal is present the capacitor starts to charge up and voltage gradually increases, when no signal is present the capacitor gradually discharges and voltage decreases.
The voltage output is fed into a transistor based Schmitt trigger arrangement (Tr3 + Tr4), when the voltage rises to the set threshold the Schmitt trigger turns on the LED and off when the voltage falls below the set threshold.