Not personally being sensitive to reading offensive things, I thought trigger warnings were a little silly, like some other posters here.
However, I recently read http://slatestarcodex.com/2014/05/30/the-wonderful-thing-about-triggers/, which makes many excellent points including that having “trigger warnings”/“content notes” can open up the possibilities of what we can write. It’s slightly harder to argue something should be banned for its obscenity when it warns those who would be offended to stay away.
Also, people are sensitive creatures. If we can be accommodating of each other by making small changes in our behavior that we might not completely understand the need for or even agree with, but which seem to make other people happier, that seems like a win.
I remember that Interstate Zero, which contained a “Contains some adult content” warning had its source code pulled because someone was reading the source code (not playing the game) and was very offended. So this isn’t a new issue.