A destroy waypoint is activated (completed) when the unit to be destroyed has been destroyed. You use the waypoint by placing it on the target. You have to double click to place the waypoint, otherwise you'll just select the target instead.
For synchronized waypoints and triggers, the group will move to the waypoint and wait until the trigger is activated. Then, the waypoint will be considered to be activated and the group will go to the next waypoint. Any code in the waypoint's On Activation field will not be executed until the trigger is activated.
As for hold waypoints, I've never used them, so someone else will have to answer that question.