Interbase to ms sql

[Consistant]

i need help with trigger, i've look online and there's nothing out there that trying to do what i do... so if u could just tell me that it can't be done in ms sql then please do so... cause i'm just about to give up on this.

below is the interbase code

SET SQL DIALECT 3;
 
SET NAMES NONE;
 
 
SET TERM ^ ;
 
 
CREATE TRIGGER ASSET_BI FOR ASSET
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN   
  /* use parent's label  */
  IF(New.parentID IS NOT NULL) THEN
    SELECT label FROM Asset WHERE assetID = New.parentID INTO New.label;
 
END
 

and i want this to be doing the same in ms sql
the asset table join to itself...

so exactly what i need 2do is if the parent id is being pass through the insert, then do a look up for the name in the asset table and replace it with the name i'm trying to insert...

any comment is much appriciated
i've spend a whole days and night to try and figure this out... now i'm tired and just about to give up...

thank you in advance