This is based on opinion. Please don't list it on a work's trope example list.
Tear Jerker / Star Trek: First Contact
"You broke your little ships..."
It's crazy but... the sequence early in the film, with the en masse attack against the Borg cube and Worf's crew ready to commit suicide in a last ditch attempt to slow it down. And then there's the look of combined disbelief and hope on the crew's faces as they realize which ship just arrived on the battlefield, and suddenly, the whole audience knows it's going to be okay.
Pretty much everyone who got hit by a Borg was this. The Enterprise crew has always been portrayed as a "family" of sorts (I keep thinking back to that otherwise generic early episode of TNG where Picard tells an orphaned boy that on this ship no one is alone). So you know that all these people MEAN something to each other, and every one lost is another blow.
Lieutenant Hawk's assimilation is even more of a tearjerker if you've read the Star Trek Titan novels and know that he had a partner who still hasn't fully accepted the reality of his death six years later.
Picard's epic 'hold the line' speech, and his subsequent realization at how his actions were tainted because of his need to hurt the Borg. Picard, a captain known for both his courtesy and calm manner, lashing out verbally and physically and then realizing what he'd become, is one of the most powerful and emotional sights in any medium of Trek.
Picard raging at Worf and calling him a coward. A coward. Michael Dorn's acting is good enough to show the Son of Mogh feeling equal parts fury at such disrespect and genuine hurt that his captain, of all people, would question him.
Picard, demonstrating why even Kirk fanboys respect him, shows what it means to be a big man and fully swallows his ego and apologizes to Worf.
While it's played for laughs, Zefram Cochrane's feeling overwhelmed by his place in history, to the point of running, is a bit sad.
The novelization reveals that Cochrane was a successful physicist before the Third World War disrupted his supply of the medication he needed to treat his mental illness (stated to be bipolar disorder, which includes periods of mania and depression). He self-medicates with alcohol, which many people who have depression and lack access to medication do today as well.
The novelisation looks into the head of Lily too, dealing with how her mother died of Cancer prior to the novel. This is tragic enough, but what makes it even more so is that just prior to WW 3, treatment for Cancer had become absolutely routine. It could be cured with a shot and treated more easily than a simply infection. Lily isn't even worried at first when her mother says she thinks she has cancer - it's only when they go to hospital after hospital and find themselves unable to get treatment that would've been such an easy fix before the planet went to hell... And then it's too late. The line So mom died of Cancer is even more tragic because it shouldn't have happened. It's the Star Trek Universe's equivalent of dying of Consumption.
Data's confession at the end of the film always gets this troper. Just "0.68 seconds". He was so. Damned. Close. To having what he'd always wanted. He didn't betray his crew and captain, of course he didn't, but when he says that this short a time, for an android "is nearly an eternity", you realise that 0.68 seconds was the equivalent of a guy spending a year or two of their life debating whether or not condemning the future to destruction is worth gaining the one thing they've always longed for.note It Makes Sense in Context. His positronic brain probably processes trillions of impulses a second. That's a lot to go through. And for just one horrific moment, I actually thought he'd do it.
Hell that entire frickin' scene with Data and Picard is equal parts Tear Jerker and Heartwarming. That's the kind of storytelling that only a decade worth of acting together in roles that are virtually a part of you, gets you.
On a meta-level, the amicable meeting of the humans and Vulcans and the future it signals serves as a reminder that a weird cult sci-fi beleagured by hammy acting and suspect VFX that was cancelled after three seasons became the cornerstone of one of the greatest (if not the greatest) fiction franchises of all time.
Also meta-level is how it's the Vulcans who are the first species that humanity meets among the galactic citizenry. Despite what we later learn about the human-Vulcan relationship in Star Trek: Enterprise, it's a major thing for the fans. Our first alien in the series was Spock. How could it have been any other race in universe?