Does motion sensor work for optical drive?

The Sudden Motion Sensor (SMS) feature of the MacBooks is made possible by an accelerometer in the notebook itself that's connected directly to the motherboard. When triggered (by a given number of degrees rotation over a certain amount of time), the Mac issues a "park drive heads now" command that any notebook drive connected to the default SATA cable will obey, regardless of whether said drive features an accelerometer.

Many modern hard drives do ship with an integrated accelerometer to detect sudden motion and park the drive heads (and occasionally these drives cause system crashes / kernel panics resulting from the redundant engagement of both the drive-based and system-based motion protection features, which require the Sudden Motion Sensor (SMS) to be disabled via a Terminal command).

I ran my MacBook Pro 17-incher (Mid 2010) with an SSD in place of the hard drive with the original hard drive swapped into the optical bay, and I immediately noticed the absence of the distinct "click" noise of the drive safely disengaging when I moved the Mac, indicating that the optical drive SATA connection is outside the jurisdiction of SMS.

So, my advice would be to connect your rotational hard drive to the default cable, and run your SSD connected to the optical drive cable, which has equally fast throughput while allowing your more fragile, non-solid-state drive benefit from the SMS feature if there isn't an accelerometer in the drive itself.

There's some good info on Wikipedia.