I'm trying to understand the following:
Thinking of a line bundle as a bunch of locally generating sections together with transition functions (which in this case are just multiplication by local sections of the structure sheaf) I wonder what it means that some power of a line bundle is trivial. I guess an example with $N>1$ would answer my question.
Thank you.
On
This is meant as a complement to Remy's nice answer.
The first topological example is the Mobius bundle $L$ on $S^1$ - the transition functions can be taken as multiplcation by $1$ and $-1$. Squaring a line bundle squares the transition maps, so $L^{\otimes 2}$ is trivial. I think it's possible to make an algebraic version of this example by following the lead of example (4) on page 22 of this book.
There are also algebraic examples in 6.5.1 of Hartshorne: if you take $U = \mathbb P^n \setminus C$ where $C$ is the zero set of a degree $d$ homogeneous polynomial, then the line bundle $L$ on $U$ corresponding to the hyperplane divisor of $C$ will have $L^{\otimes d}$ trivial.
For example, in $\mathbb P^2$ let $C = V(x^3 + y^3 + z^3)$ and take the line bundle $L$ on $U$ corresponding to the hyperplane $x=0$, say. Then $L^{\otimes 3}$ will be trivial essentially because of the existence of the globally defined regular function $\frac{x^3}{x^3+y^3+z^3}$, whereas $L^{\otimes 2}$ will be non-trivial (there is no globally defined regular function of the form $\frac{x^2}{p}$).
Also, in this question it was noted that for normal projective varieties over $\mathbb C$ there exists a torsion line bundle if and only if $H_1(X,\mathbb Z)$ is non-zero, so they are quite common.
On
Just a small remark, complementing the two nice answers that already exist.
One can think of torsion in $\mathrm{Pic}(X)$ as detecting non-trivial classes in the fundamental group of $X$. More rigorously, I'm thinking of the generalization of standard Kummer theory. Namely, let's suppose (for convenience sake) that $X/k$ is a projective variety and that $m$ is invertible in $k$. Then, one has the following Kummer sequence of (étale) sheaves on $X$:
$$1\to\mu_m\to\mathbf{G}_m\xrightarrow{[m]}\mathbf{G}_m\to 1$$
Passing to the long exact sequence in (étale) cohomology gives one the following short exact sequence
$$1\to \mathcal{O}_X(X)^\times/(\mathcal{O}_X(X)^\times)^m\to H^1_{é \text{t}}(X,\mu_m)\to \text{Pic}(X)[m]\to 1$$
In particular, if we assume, for example, that $k=\overline{k}$, and that $X$ is integral, then
Thus, we see that we have an isomorphism
$$\text{Hom}_\text{cont.}(\pi_1^{é\text{t}}(X),\mathbb{Z}/m\mathbb{Z})\cong \text{Pic}(X)[m]$$
which shows precisely in what way torsion line bundles are picking information about the covering theory of $X$.
If one wants to be concrete, the isomorphism described above sends an $m$-torsion line bundle $\mathscr{L}$ to the finite étale cover
$$\mathbf{Spec}(\mathcal{A}(\mathscr{L}))\to X$$
where $\mathcal{A}(\mathscr{L}))$ is the sheaf of quasi-coherent $\mathcal{O}_X$-algebras given by
$$\mathcal{A}(\mathscr{L})=\bigoplus_{i=0}^{m-1}\mathscr{L}^{-i}$$
(I hope I didn't make a mistake in putting dual!) with multiplication which shuffles sections up the chain and then cycles via the choice of an isomorphism
$$i:\mathscr{L}^{\otimes m}\xrightarrow{\approx}\mathcal{O}_X$$
This also shows where, really, units modulo their $n^\text{th}$-powers are coming into play since this isomorphism is non-canonical, and so depends on some choice in this quotient group.
Flavor remark: The above is really just a classification of $\mu_m$-torsors as pairs $(\mathscr{L},i)$ of an $m$-torsion line bundle and an isomorphism $i:\mathscr{L}^{\otimes m}\xrightarrow{\approx}\mathcal{O}_X$.
Example. If $(E,O)$ is an elliptic curve over an algebraically closed field $K$, then we know that the natural map \begin{align*} E(K) &\to \operatorname{Pic}^0(E) \\ P &\mapsto \mathcal O_E(P - O) \end{align*} is an isomorphism of groups. Thus, if $P$ is a torsion point of $E$ (they always exist: for example over $\mathbb C$ the group $E(\mathbb C)$ is a complex torus $\mathbb C/\Lambda$, for $\Lambda \subseteq \mathbb C$ a lattice, so halves of lattice points give $2$-torsion elements), then $\mathcal O_E(P - O)$ is an $n$-torsion line bundle.
Remark. If $A$ is an abelian variety over $K = \bar {\mathbb F}_p$, then every point of $A$ is torsion. This follows because any point $P \in A(K)$ is defined over a finite field $\mathbb F_q$, and the group $A(\mathbb F_q)$ is finite.
In particular, applying this to $\operatorname{Pic}^0(X)$ for any smooth projective variety $X$ shows that every line bundle rationally equivalent to $\mathcal O_X$ on a smooth projective variety over (the algebraic closure of) a finite field is torsion. Presumably the citation you gave uses a variant of this statement, for varieties over $\mathbb Z/p^n\mathbb Z$.
Remark 1 (thanks to Alex Youcis). I of course have to assume something like $\mathscr L$ is rationally equivalent to $\mathcal O_X$, because otherwise the result I claim is clearly false: think for example about $\mathcal O_{\mathbb P^n}(1)$.
Remark 2. Because Alex Youcis asked: the precise statement about representability of $\operatorname{Pic}^0_X$ for flat projective $X \to \operatorname{Spec} \mathbb Z/p^n\mathbb Z$ with geometrically integral fibres is proven in FGA Explained, Theorem 9.4.8. But this is probably much beyond the type of material the OP is comfortable with.
Short story: it works also over $\mathbb Z/p^n\mathbb Z$, but my proof is fairly elaborate. There might be a much easier argument that I'm unaware of, but this is the standard answer that most algebraic geometers will pull out.