How is nucleation triggered?
There are, as I see it, three major pathways for nucleation of supercritical carbon dioxide in water.
As @Michiel points out correctly in the comments, tapping a glass bottle may cause cavitation, which is then the initial nucleation site for the (runaway) bubble formation. However, I think it is noteworthy that not every type of tapping leads to this; It is when you tap on top of an open bottle with a hard object (such as another bottle) that the gas formation is strongest. This is because the shock waves propagating through the glass walls of the bottle meet up at the bottom of the bottle for a huge impulse into the liquid, forming a larger cavity than other methods (don't do this at parties, people will hate you and their beer will be stale). The relative difference of impulse can be immediately grasped from the fact that the tapping bottle will not start foaming, while the tapped bottle most definitely will.
There are however alternative means of containing beverages, for example aluminium cans. Due to the form of the cans, cavitation formation is highly unlikely. More likely, deformation of the can upon dropping will provide the nucleation sites for the carbon dioxide to form bubbles; think jagged edges on the inside, reactive defect sites in the lattice, and a possibly broken oxide layer of the aluminium, exposing highly reactive elemental aluminium for a short time.
Shaking: Air Dissolution
As @Nikolau correctly pointed out in the comments, shaking a bottle will lead to small bubbles of air getting trapped in the liquid and providing nucleation sites for the gas. Nothing magical about that, but I gave it a heading anyway.
Onwards to the second question you had:
Waiting: Why no foam?
The reason that waiting leads to less foam and spewing around of your favourite carbonated beverage is twofold.
As explained in the linked question on Physics.SE, the bubbles will rise to the top of the liquid level. As such, releasing the pressure will cause further nucleation primarily at the surface and not within the liquid, leading to less liquid propagation by gas evolution. In plain English: This means you don't get soaked by the beverage as it is propelled out of the opening, because the gas evolution happens at the surface and "forces" the liquid to stay in place.
The pressure inside the container rises as more gas is formed. If you wait, a new equilibrium state will be reached by redissolving some of the carbon dioxide that nucleated out of the solution, subsequently lowering the pressure again. (I don't think that the pressure goes back to the initial level within a finite amount of time, but I may be wrong here. If someone could clarify this I would be grateful.)
As there are now less nucleation sites, combined with the fact that they rise to the top (see point 1) foam production is severely hindered.