The victory lifted Carnegie above Nottingham in the Championship table and will send them into Friday’s derby at Doncaster Knights in high spirits.
The sides went into the match level on points and Nottingham looked set to claim the spoils until Matthew Everard was sent off for a second yellow card late in the second half.
Yorkshire took advantage of the extra man to snatch two tries in the final 12 minutes, overturning a 10-0 deficit.
The hosts trailed 7-0 at the end of a dull opening period, after conceding a try before they had held the ball.
Nottingham forced a scrum close to the line after just four minutes and No 9 Murray McConnell forced his way over.
Tiff Eden converted, but missed with a simple penalty attempt nine minutes later.
The only other chance came soon afterwards when the fly-half kicked to the corner, but Will Robinson could not gather the ball.
The visitors were reduced to 14 players late in the first half when Everard was sin-binned for collapsing a maul, but Carnegie could not take advantage and went 10-0 behind to a long-range Eden penalty soon after the break.
Carnegie snatched a lifeline on 68 minutes when Everard was dismissed after being yellow carded for the same offence as his earlier sin-binning.
Yorkshire captain Richard Mayhew scored from close range moments later and Alex Davies added the extras.
Then, with two minutes remaining, Carnegie won the lineout from a penalty near Nottingham’s line and the impressive Joe Buckle forced his way over to edge the home side ahead.
Davies’ conversion attempt bounced away off a post and he missed with a penalty on the final kick of the game.