SQL Trigger to Insert a row in Table B is the record is inserted in Table A I have a requirement where I have to Insert a Record into Table &quot;Site&quot; after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for single record scenario but failing when I insert multiple records at a time. Here is the trigger code. Please gjuide me to fix this issue.
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update 
AS


DECLARE @SiteId varchar (10),
       @SiteName varchar(60),
       @Description VARCHAR(200),
       @DeleteIndicator bit,
       @ExternalKey VARCHAR(400); 

SELECT @SiteId = ins.SITEID FROM INSERTED ins;
SELECT @SiteName = ins.SITENAME FROM INSERTED ins;
SELECT @ExternalKey = ins.MV2ExternalKey FROM INSERTED ins;
;
set @DeleteIndicator = 0;


MERGE Site AS [target] 
USING (SELECT [i].MV2ExternalKey FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET 
         Name = @SiteID,
         Description = @SiteName,
         DeleteIndicator = @DeleteIndicator
    
WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator]) 
VALUES (@SiteId,
    @SiteName,
    @ExternalKey,
    @DeleteIndicator );
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