Edit: Here is a possible characterization. As mentioned in the comments above, the vanishing of the 1-truncated cotangent complex $\tau_{\leq 1}L_{B/A}$ of a map of rings $f \colon A \to B$ is equivalent to a lifting property with respect to square-zero extensions $T' \to T$. This follows from the fact that the space $Map(L_{T/A},M[1])$ is equivalent to the groupoid of square-zero extensions of $T$ over $A$ with kernel $M$. Here $M$ is a $T$-module.
Rephrasing this, $\tau_{\leq 1} L_{B/A}$ vanishes if and only if $f$ has a lifting property with respect to morphisms of 0-truncated simplicial algebras such that the kernel is concentrated in degree 0 and squares to 0. Let's call these 0-concentrated.
Then we can go on to look at morphisms of 1-truncated simplicial algebras with kernel $K$ concentrated in degree 1. The squaring-to-zero property is vacuous here, because a product of two elements in $\pi_1(K)$ will be in $\pi_2(K)$, which is zero by assumption. Let's call these 1-concentrated Then we find that $\tau_{\leq 2} L_{B/A}$ vanishes if and only if $f$ has a lifting property with respect to all 0- and 1-concentrated maps. This again holds because the cotangent complex classifies 0- and 1-concentrated maps.
I think now it's clear how to go on: $\tau_{\leq n+1}L_{B/A}$ vanishes if and only if $f$ has a lifting property with respect to all $m$-concentrated maps with $m \leq n$. And the full cotangent complex vanishes if and only if $f$ has the lifting property with respect to $n$-concentrated maps for all $n$.
These directions one looks at if one starts to check with respect to $n$-concentrated maps for $n \geq 1$ are sometimes called the derived directions. So Avramov's theorem might be rephrased as saying that under strong finiteness assumptions, unobstructedness in the classical directions implies unobstructedness in all derived directions.
This is an anwer to your last paragraph about L-trivial maps in other geometric categories. If you are only interested in schemes it doesn't tell you anything interesting.
One thing that the cotangent complex is good at is measuring connectivity of a morphism of simplicial rings. This also holds without any finiteness assumptions on the ring.
Recall that a morphism $f \colon A \to B$ of simplicial rings is called n-connective if it induces isomorphisms $\pi_i (A) \to \pi_i (B)$ in degrees $< n$ and a surjection $\pi_n (A) \to \pi_n (B)$ in degree $n$. There then is a result that states that if $f$ is $n$-connective, then the homology of the relative contangent complex $L_{B/A}$ vanishes in degrees $\leq n$. (I hope I got all the indices right.) So in particular, any equivalence of simplicial rings is L-trivial.
One way of intepreting your question is to ask when the converse holds. What do I know if a morphism of simiplicial rings is L-trivial? There is a partial converse to the statement above. Namely, if a morphism $f \colon A \to B$ induces an isomorphism $\pi_0(A) \to \pi_0(B)$ and is L-trivial, then it is an equivalence! I find this pretty suprprising, as L is only a linear piece of data, but still manages to detect equivalences.