SQL Trigger to Insert a row in Table B is the record is inserted in Table A
Just use your &quot;source&quot;  as supplier for it.
Because you are using variables , which holds only one value , this is way is working fine with one statement.
MERGE and trigger statements can work with multiple rows at once, not just one , so just use the special INSERTED table inside the MERGE statement; just replace the your MERGE statement with next MERGE statement, inside your trigger code
Here are some samples : example1 ,
example2
MERGE Site AS [target] 
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName,@DeleteIndicator as DeleteIndicator FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET 
         Name = source.SITEID,
         Description = source.SITENAME,
         DeleteIndicator = source.DeleteIndicator
    
WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator]) 
VALUES( source.siteID,source.siteName,source.MV2ExternalKey,source.DeleteIndicator)

so , the trigger code will become:
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update 
AS


MERGE Site AS [target] 
    USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source
    ON ([target].Name = [source].MV2ExternalKey)
    WHEN MATCHED THEN
    UPDATE  SET 
             Name = source.SITEID,
             Description = source.SITENAME,
             DeleteIndicator = source.DeleteIndicator
        
    WHEN NOT MATCHED THEN
    INSERT ([Name],[Description],[ExternalKey],[DeleteIndicator]) 
    VALUES( source.siteID, source.siteName, source.MV2ExternalKey, source.DeleteIndicator);

As @Charlieface mentioned in comment section, in case of an update with the same values, some people, will not perform update.
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source

will become
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i] 
EXCEPT 
SELECT d.MV2ExternalKey,d.SiteID,d.siteName, 0 as DeleteIndicator FROM DELETED AS d) AS source 

ps: there are some bugs on merge statement, so be careful
link1, link2