Elementary SQL STDistance Implementation?

Question

I know that with SELECT @source.STDistance(@target)I can derive the distance between two spatial objects, and more complex with something like this, where I can wrap up nearest neighbor in a trigger and get values from nearest neighbor. I'm afraid I may have over-complicated this to the point where I can't figure out something as simple as getting not only the "name" of nearest neighbor, but how far it is to it? How would I extract that distance as part of this and write that value along with the name?

ALTER TRIGGER [dbo].[IMPORT_RAW_WILD_BEAR_GPS_COLLAR]
ON [dbo].[WILD_BEAR_GPS_COLLAR]
after INSERT,UPDATE NOT FOR REPLICATION
AS
BEGIN
   SET NOCOUNT ON;
  UPDATE p SET 
         SHAPE = CASE WHEN i.SHAPE IS NOT NULL  
        THEN p.SHAPE ELSE Geography::STPointFromText('POINT(' 
          + CAST(p.LON AS VARCHAR(20)) + ' '  
          + CAST(p.LAT AS VARCHAR(20)) + ')', 4269) END, 
      LON = CASE WHEN p.SHAPE IS NULL THEN p.LON ELSE p.SHAPE.Long END, 
      LAT = CASE WHEN p.SHAPE IS NULL THEN p.LAT ELSE p.SHAPE.Lat END,
    QuadName = COALESCE(b.name, p.QuadName),
    Watershed = COALESCE(c.HUC_12_Name, p.Watershed),
    County = COALESCE(d.Name, p.County),
    State= COALESCE(e.Name, p.State),
    NEAR_ROAD = COALESCE(k.FULLNAME, p.NEAR_ROAD),
    NEAR_TRAIL = COALESCE(j.NAME, p.NEAR_TRAIL),
                UNITCODE = 'WILD',
                RESTRICTION = 'UNR',
                UNITNAME = 'WILD',
                DATUM = 'NAD83',
                COORD_SYSTEM = 'GCS',
                COORD_UNITS = 'dd',
                UTM_ZONE = '17',
                NEAR_STREAM = COALESCE(g.GNIS_Name, p.NEAR_STREAM)
                ELEVATION = (SELECT pdata.getValueByLoc(1,p.SHAPE.Long,p.SHAPE.Lat)  FROM [dbo].[DEM10MP])
  FROM  WILD_BEAR_GPS_COLLAR
     AS p

  INNER JOIN 
    inserted AS i
    ON i.OBJECTID = p.OBJECTID
  LEFT OUTER JOIN USGS_24K_TOPOMAP_BOUNDARIES AS b
    ON b.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WATERSHEDS AS c
    ON c.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WILD_COUNTIES AS d
    ON d.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WILD_States AS e
    ON e.Shape.STIntersects(i.Shape) = 1

CROSS APPLY (SELECT TOP 1 GNIS_Name, shape                  
FROM dbo.NHDFLOWLINE WITH(index ([NHD_idx]))                 
WHERE NHDFLOWLINE.Shape.STDistance(i.Shape) IS NOT NULL
ORDER BY NHDFLOWLINE.Shape.STDistance(i.Shape) ASC) as g
CROSS APPLY (SELECT TOP 1 RiverOrder, shape                  
FROM dbo.NHDFLOWLINE WITH(index ([NHD_idx]))                 
WHERE NHDFLOWLINE.Shape.STDistance(i.Shape) IS NOT NULL
ORDER BY NHDFLOWLINE.Shape.STDistance(i.Shape) ASC) as h
CROSS APPLY (SELECT TOP 1 FULLNAME, shape                    
FROM dbo.WILD_ROADS 
/****** force spatial index hint ******/ 
WITH(index ([WILD_ROADS_idx]))                   
WHERE WILD_ROADS.Shape.STDistance(i.Shape) IS NOT NULL 
ORDER BY WILD_ROADS.Shape.STDistance(i.Shape) ASC) as k
CROSS APPLY (SELECT TOP 1 NAME, shape
FROM dbo.WILD_TRAILS
/****** force spatial index hint ******/ 
WITH(index ([WILD_TRAILS_idx]))                   
WHERE WILD_TRAILS.Shape.STDistance(i.Shape) IS NOT NULL 
ORDER BY WILD_TRAILS.Shape.STDistance(i.Shape) ASC) as j

END;

Answer 1

Answer dug up from an old post I remember when trying to solve another nearest neighbor problem:

update s 
set 
[NEAR_TRAIL] = fname,
[DIST_TRAIL] = Shape.STDistance(fshape)
from(
Select
[dbo].[GRSM_BEAR_GPS_COLLAR].*,
fnc.Name as fname,
fnc.Shape as fShape
from
[dbo].[GRSM_BEAR_GPS_COLLAR]
CROSS APPLY (SELECT TOP 1 Name, shape                   
FROM [dbo].[GRSM_TRAILS] WITH(index ([GRSM_TRAILS_idx]))                
WHERE [GRSM_TRAILS].Shape.STDistance([dbo].[GRSM_BEAR_GPS_COLLAR].Shape) IS NOT NULL
                  ORDER BY GRSM_Trails.Shape.STDistance([dbo].[GRSM_BEAR_GPS_COLLAR].Shape) ASC) fnc)s;