I'm late to this party but here's my 2p:
I've had two transrectal biopsies, both under a local anaesthetic (which were no worse than you'd suffer at the dentist). Neither was painful as such, just uncomfortable, especially #2.
Of course, the most uncomfortable aspect, for me at least, was enduring such an intimate procedure and trying desperately to make conversation with a radiologist who's almost certainly heard all the jokes before!
I was well-informed about what to expect afterwards and am surprised to read that some people have had such a bad experience in that respect.
The first wasn't very uncomfortable at all really. Just the occasional 'tugging' feeling as the samples were taken. My back passage was a bit sore as the anaesthetic wore off and passing the first stool was 'memorable'. I had blood in my urine for a couple of days and had one ejaculation with blood in the semen.
The second was less 'enjoyable' and I was in quite a lot of discomfort for a day or two. The blood in the pee was about the same as the first but the blood in the semen lasted for a few weeks.
I guess that the first guy was just a bit gentler than the second.