How does trigonometric substitution work?

It might help to not write $x$ for the before and after. That's misleading. What you're doing is writing $x=3\sin(\theta)$. You're inventing a new variable $\theta$ that relates to $x$ in a way that lets you use trig identities to solve your problem. Plugging in values after your transformation shouldn't give you the same values, since the new integral is in a different variable. We have: $$x=3\sin(\theta)$$ $x$ is a function of $\theta$. Differentiating we get: $$\frac{dx}{d\theta}=3\cos(\theta) \implies dx=3\cos(\theta) d\theta$$

So rewriting the integral in terms of our new variable, we get

$$\int \frac {\sqrt{9-9\sin^2(\theta)}}{9\sin^2(\theta)}\cdot3\cos(\theta)d\theta$$

Now we can use trig identities to evaluate this new integral and get rid of the troublesome square root, which is the reason we bothered with this substitution in the first place.

$$\int \frac {\sqrt{9\cos^2(\theta)}}{9\sin^2(\theta)}\cdot3\cos(\theta)d\theta$$ $$\int \frac {\cos^2(\theta)}{\sin^2(\theta)}d\theta$$ expanding the numerator as $1-\sin^2(\theta)$: $$\int \csc^2(\theta)-1 d\theta$$ evaluating, we get: $$-\cot(\theta)-\theta+C$$

But our integral was in terms of $x$. While this substitution was helpful, we're only halfway done, since we don't know what the expression looks like in our original variable. So now we need to sub in our original variable. $x=3\sin(\theta)$, so to get things in terms of $x$ we'd like to get as many $\sin(\theta)$s as we can. $\cot(\theta)=\frac{\cos(\theta)}{\sin(\theta)}=\frac{\sqrt{(1-\sin^2(\theta)}}{\sin(\theta)}$. By our substitution, $\sin(\theta)=x/3$. So we get $$-\frac{\sqrt{1-\frac {x^2} 9}}{\frac x 3}-\theta+C$$ We still have a $\theta$ we need to get rid of. To do this, we can just reverse our substitution. That is, $x=3\sin(\theta) \implies \theta=\sin^{-1}(x/3)$. C is still an arbitrary constant and so can stay. After simplifying the fraction, we get: $$-\frac{\sqrt{9-x^2}}{x}-\sin^{-1}(x/3)+C$$ Which is the answer. Hopefully this clears some things up.

The following theorem is useful to keep in mind when making substitutions:

If function $x=\phi\left(t\right)$ satisfies the following conditions:

1) $\phi\left(t\right)$ is a continuous one-to-one function defined on the interval $\left[\alpha,\beta\right]$ and having a continuous derivative there.

2) values of $\phi\left(t\right)$ are contained within the interval $\left[a,b\right]$

3) $\phi\left(\alpha\right)=a$ and $\phi\left(\beta\right)=b$

Theorem. then for every function $f\left(x\right)$ continuous on $\left[a,b\right]$ the following formula for the change of variable in a definite integral holds:$$\int_{a}^{b}f\left(x\right)dx=\int_{\alpha}^{\beta}f\left[\phi\left(t\right)\right]\phi'\left(t\right)dt$$

You must use a name other than $x$ when you substitute, maybe say let $x=3\sin t$. (It makes no sense to let $x=3\sin x$.) Using $x$ for the substitution is probably one of the big reasons for being confused about the process.

We have then $dx=3\cos t\,dt$, and $\sqrt{9-x^2}=\sqrt{9(1-\sin^2 t}=3\cos t$. So we arrive at $$\int \frac{\cos^2 t}{\sin^2 t}\,dt.$$ To evaluate, use $\cos^2 t=1-\sin^2 t$. So we want $\int \frac{1-\sin^2 t }{\sin^2 t}\,dt$.

This simplifies to $\int \left(\frac{1}{\sin^2 t} -1\right)\,dt$, and then to $\int(\csc^2 t-1)\,dt$.

Now it is useful to remember that $-\cot t$ is an antiderivative of $\csc^2 t$. We conclude that our integral is equal to $$-\cot t + t+C.$$ Finally, we want to go back to $x$. Remember that $x=3\sin t$, so $\sin t=x/3$, and therefore $t=\sin^{-1}(x/3)$.

To express $\cot t$ in terms of $x$, use the fact that $\frac{\cos t}{\sin t}$. We have $\sin t=x/3$, and $\cos t=\sqrt{1-(x/3)^2}=\frac{1}{3}\sqrt{9-x^2}$. When we put the pieces together we get the expression that you quoted.