The reason V7's do that is because there are two half-step intervals in the scale pattern: between the 3rd and 4th notes, and between the 7th and the tonic. The tonic chord, the chord that has the most consonance and least tension (relative to the key), has both the tonic and the 3rd, and so these exert a "pull" on the 4th and 7th notes. In a V chord, its 3rd is the 7th note of the scale, and so that "voice" wants to pull up to the tonic. (This sometimes happens early, as in the progression V - Vsus4 - I.) In V7, the added 7th is the 4th note of the scale, which wants to pull downward to the 3rd, so the V7 chord has even stronger pull to the tonic. It also contains a dissonant tritone interval (diminished 5th) between the 3rd and the 7th--in fact, those two notes alone are sometimes sufficient to suggest the whole chord. That dissonant interval created tension, increasing the draw to the tonic chord for resolution and a release of tension. Tension and its release (resolution) are behind a lot of what goes on in music.
As you surmised earlier, in a key change progression like E - A(7) - D, the pivot chord assumes two interpretations at the same time. From the perspective of the original key (E), the progression E - A looks like a standard I - IV progression. From the perspective of the new key, A is a V chord leading to I. When you add the 7, however, you narrow the expectations: a IV chord is most often followed by a move back to I or a move to V, or less often a move to ii or vi (chords it shares two notes with) or to bVII (fifths progression). But outside of blues, you don't see IV7s much, and A7 has that strong pull to D, which would be bVII from the perspective of the old key. If bVII (or vm) hasn't appeared much in the old key, then when you hit this chord, there's a good expectation you're shifting tonality, specifically to D.
This discussion indicates one of the pitfalls of the relational I-IV-V type of notation: it's relative to the tonality (key) and mode, so how do you refer to things when the tonality is shifting?? If the shift is brief, you largely pretend it hasn't happened (hence notation like bVII or #I). But if the key change is more protracted or you're trying to shine the light on a cascading pattern, you may have to indicate the tonality shift more explicitly, like saying on the A7 chord "IV7=V7" (i.e. "this is a pivot chord in a tonality change") or "D:V7".
Similarly, in relative notation, major and minor scales have different third, sixth and seventh notes, so when you say VII in a minor key, you mean the equivalent of bVII in a major key, i.e. the major chord rooted a whole step rather than a half step below the tonic. This means that you don't have to write extra flats before the Roman numerals in minor keys, and much of the theory doesn't have to be specially qualified by mode, but it can be confusing.
