As said previously, wet moats can be more susceptible to infiltration than dry ones. But, you can have security and flavor simply by digging your moat more than 1 layer deep, and making sure that (at least) the topmost layer is dry. A note, however: Toady has dropped hints that the coming update will feature climbers, and/or give existing creatures the ability to climb. So, while a simple 1-layer high "wall" made of sand is currently enough of an obstacle to bring an army of bronze colossi to a standstill, that may change in a BIG way come the update. So in short, higher walls + deeper moats = better.
The prospect of filling your moat with traps and then water is an appealing one, but I don't know what might happen if/when the water freezes--it could possibly destroy the traps.
As for water itself: To fight evaporation, move it short distances at a time, and in sections. Instead of putting the pump at one Murky Pool and running a pipeline halfway across the map, dig little pools to act as "way stations" in between, and just pump from each one into the next. This way, the water spends far less time at depths of 1/7th and 2/7ths--the only depths at which water will evaporate. Similarly, divide your moat into subsections. As each one gets filled, open the floodgate (or tear down the wall/whatever) to allow the water to flow out & get the next section half full. Repeat.