Do those traffic cameras come on when they turn yellow or when red if/when a car drives over the sensor?

Most of them are VIDEO cameras that are on constantly. When the sensor is triggered, the recording is flagged for review by an officer. If the officer determines a violation occurred, a ticket is issued.