I dont think that question should be put here, but rather in the triggers section. I have a function to detect that though. Vexorian's IsPointWater didn't work so well for me for some reason, he used TERRAIN_PATH_AMPHIBIOUS or something like that.

JASS:
if IsTerrainPathable(GetUnitX(u), GetUnitY(u), PATHING_TYPE_FLOATABILITY) then
<add dust effect here>
elseif not IsTerrainPathable(GetUnitX(u), GetUnitY(u),PATHING_TYPE_WALKABILITY) then
<add water effect here>
endif
I suggest attaching it to the right foot of the unit, looks best.
Edited by Blade.dk. Reason: Jass tags.