Wouldn't that be more easily accomplished by having a floodgate in the floor?
i.e. a hatch?
Personally, I don't see why it shouldn't work, except that I've had difficulties with hatches in the past[1] which means I avoid them[2].
Alternatively, a retracting bridge should work much the same. (Minus time for workshop construction of hatch, plus time for architectural design of said bridge. Either way, a little extra effort, but then again the bridge cannot be designed ahead of time, whereas a hatch can be manufactured just about as soon as one has the inkling it'd be needed.)
But, either way, you still need the "on entity striking hatch, operate something" switchy-thing. Unless you go the 'dumb' route and just hook the respective mechanicals it into a longish-period repeater, of course.
[1] They just don't seem to open when you want them to, and though I'm not totally sure I didn't forget to wire them up to the lever, I was left with the impression that they don't like opening when you have a high pressure of water upon them (say 10-20Zs worth of accumulated water). But I'm fairly sure that they've not been designed that way, by Toady, so I'm still left with the possibility that, of all openable water-barriers that I use (floodgates, doors, raising bridges), I
always forget to connect up my floor hatches to the appropriate levers before filling the huge cisterns they are at the bottom of. (Yes, I could try to select a new connection, unfulfillable as it would be ("fillable" being the very problem), to see if it's allowed from that lever to that hatch and therefore not already been done. For some reason, I've never bothered to check that at the time, always thought about it far later.) It could just be that levers lock/unlock the hatch, perhaps?
[2] I currently just tend to use hatches in spots (rare, but occasionally necessary) where I've got vertically-from-below stairwell access into a channel occasionally flooded with water. When I let my dwarves wander up into it, they'll let a splash of water through (typically the 1-2/7ths that remain after an actual flood) but, as long as the traffic isn't excessive, no real danger of flooding the entryway, because it doesn't keep dripping in when no-one is actively opening and closing the hatchway at the top of the stairway.