My second worst facepalm moment came when I came under siege and ordered every ballista at my fortress (seven or so in total) to fire at will, naively assuming this meant they'd fire when they had an enemy in their sight. Not so.
My next, and biggest, face palm moment came when I had dug a deep shaft under a pond (with many, many cave-ins, wounded dwarves and casualties, hey, this was my first go at this!) and realized, after basically digging away the pond and leaving a rectangular hole where it used to be, that when you dig away ponds, you don't get water accumulation any more from rainfall on those tiles. OK, I think, so I'll build an awesome system where an underground channel connects the pool to the resident river. When I pull a lever, the door at the end of the channel opens, sending water cascading through the channel and plummeting in an awesome waterfall down into the pool several floors down. Turn the lever again, and the door shuts on the water.
Now, at this point I'd learned
something about water and flood risk. I'd flooded small sections of another fortress already, so I took precautions - I put floor tiles over the channel to keep water from running its borders and since I didn't want dwarves sucked in by the torrent of water (why I was worried about this when I had covered the opening in the floor by grates is beyond me
), I made the cut into the river diagonally from above the channel. Of course this would make water come flooding in, but I had a series of walls set for construction that would keep this leakage out.
Not. I had underestimated, greatly, the power of the water that would come flowing in. Immediately the tile next to the door was getting a torrent of 5/7 water, construction of the walls became impossible, I realized the torrent couldn't be stopped, and all I could do was build two doors to stop the flood from claiming the whole fortress.
End result: entire shaft flooded up to the level of the river, room with the door flooded to 7/7. Had I not put in the layers of safety precautions, primarily doors blocking the shaft from the rest of the fortress, my whole fortress could easily been lost. But the nightmare still wasn't over - another dwarf managed to drown himself by opening the door (sending a torrent of water into my Workshop Level that it took quite a while and a new drain to get rid of), entering the flooded pool, shutting the door behind him, and only
then realizing that the damned shaft was full of water.
But no, the cursed pool of death was not done claiming lives. I had made the best out of my situation by making the edge of the pool a statue garden and designating it as a drinking and fishing area. My dwarves loved the idyllic surroundings and seemed to have forgiven me for my big mess-up... that is, until a goblin siege arrived, I ordered everyone inside, and the goblin archers took up position on the surface at the edge of the shaft and gleefully opened fire on the dwarves taking refuge below them. Result, at least two more dead.
I now plan to reclaim the shaft by diverting the channel into the neighbourhood magma pipe, for then to pump the water out of the shaft somehow. Knowing my success so far right, I'll probably not succeed without partially flooding a floor, caving in several tiles, and leaving more miners bed-ridden or in coffins.
(edit) Had another one just now. I'm having my ballista/catapult crews fire practice shots, and valiantly scanned the wilderness to ensure no one would get hit by the arrows and rocks I fired. No prob, even though a couple animals and disturbingly many dwarves I didn't spot for whatever reason came only a tile or three from being skewered by projectiles.
Disaster struck when I after many successful catapult shots ordered my first ballista to fire. Scanned countryside, countryside clear. Fired ballista, insta-killed ballista operator's pet cow, who was hanging out with his/her owner
inside the pillbox, in front of the ballista.
Future versions could perhaps give me a "[Artillery crewman] suspends/cancels Fire Ballista: too high friendly fire risk".