This is based on opinion. Please don't list it on a work's trope example list.
Tear Jerker / Star Trek: Generations
The "death" of the Enterprise D may not be as heart-wrenching as that of the original ship, but when it's out-right stated that she can't be salvaged, that's when it hits you.
What about that slow pull back once the saucer has come to rest? There's a moment of relief at the realization that they made it through, and then it really hits you - the crew survived, but the Enterprise won't be getting back up from that one.
The "final" death of James T. Kirk, Captain of the Enterprise.
"It was the least I could do... for the Captain of the Enterprise. It was fun.... oh, my..."
Picard crying over the loss of his Nephew Rene.
Picard giving a Nexus recreation of Rene a hug.
Picard having to abandon his fantasy.
Kirk sounds so broken when he accepts that the Nexus isn't real, despite him wanting everything so much.
Cat owners identify with Data crying over finding Spot.
After seeing the Enterprise-B axillary control room Kirk was in is now just a massive hull breach:
Chekov: My God! Was anyone in here? Scotty:(solemnly) Aye.
Behind the scenes, imagine how devastated Spock and McCoy were when they got the news of there best friends (supposed) death.
William Shatner himself has said that this following line was the hardest he ever had to deliver:
Who am I to argue with the Captain of the Enterprise?
What about Scotty's report on how many of the refugees he managed to transport off the failing refugee ship?
"I got 47..... out of 150."
Sulu's daughter reading off the damage report on Engineering, hesitating over "... and Deck 15" where Kirk was, and then just sitting there shocked and horrified that the man her father's been telling her incredible stories about since she was a baby is gone. And somebody's going to have to tell her Dad about it.
Many people have questioned how a Starfleet icon like Kirk could ever seriously consider staying in the Nexus and not trying to save the day? He explains, he's spent his whole life saving the galaxy, and all it got him....was an empty house. There are several people who can relate to feeling they missed out on things because of devotion to career.
It speaks volumes that even with above, when he finally stops to think about it, he totally goes with Picard. James Tiberius Kirk will never EVER not do his job. Not even in (kinda sorta) death.
Not precisely. He goes because it's not real, not because it's not desired.