My objective is to create a trigger which fires after an UPDATE, and which set a ModifiedTimeStamp field to the current date for all and only effectively updated / inserted rows.

I had a previous version with a cursor, which "works" despite being deeply ugly:

ALTER TRIGGER [dbo].[TG_INS_UPD_Customer_ModifiedDateTime]
   ON  [dbo].[Customer]
   AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @Id INT 

DECLARE BrandModifiedDateTimeCursor CURSOR LOCAL FOR 
    SELECT
        BrandId
    FROM
        inserted

OPEN BrandModifiedDateTimeCursor

FETCH NEXT FROM BrandModifiedDateTimeCursor   
    INTO @Id

WHILE @@FETCH_STATUS = 0  
BEGIN

    UPDATE [dbo].[Brand] 
    SET  ModifiedTimeStamp = GETDATE() 
    WHERE BrandId = @Id

    FETCH NEXT FROM BrandModifiedDateTimeCursor   
        INTO @Id
END

CLOSE BrandModifiedDateTimeCursor
DEALLOCATE BrandModifiedDateTimeCursor

END

I wanted to migrate to a new version which only uses an UPDATE, clearer, shorter and way more efficient, which looked like this:

ALTER TRIGGER [dbo].[TG_INS_UPD_Customer_ModifiedDateTime]
   ON  [dbo].[Customer]
   AFTER INSERT,UPDATE
AS 
BEGIN
    UPDATE b
    SET ModifiedTimeStamp = GETDATE()
    FROM [dbo].[Brand] b
    INNER JOIN inserted i ON i.BrandId = b.BrandId
END

But for some reason I do not understand, that version fires for ALL the rows in the table instead of just updated / inserted ones, yet they are supposed to do the same thing.

Why, and what is the correct way to proceed?

Here is the MERGE query

ALTER PROCEDURE [dbo].[spSTASyncBrand]
    -- Add the parameters for the stored procedure here
    @LastModificationDate DATETIME
AS
BEGIN
    SELECT
    *
INTO
    #BrandsToMerge
FROM 
    [185.153.244.42\BC].[BEETLE_SYNC_PROD].[dbo].[vNavBrand]

MERGE dbo.Brand AS brd 
USING (SELECT * FROM #BrandsToMerge) AS src
ON (src.Code = brd.Code)  
WHEN MATCHED AND 
    (brd.[Name] <> src.[Name]
    OR ISNULL(brd.Phone,'') <> src.Phone
    OR ISNULL(brd.Fax,'') <> src.Fax
    OR ISNULL(brd.ContactEmail,'') <> src.ContactEmail
    OR ISNULL(brd.AccountingEmail,'') <> src.AccountingEmail
    OR ISNULL(brd.Address1,'') <> src.Address1
    OR ISNULL(brd.Address2,'') <> src.Address2
    OR ISNULL(brd.ZipCode,'') <> src.ZipCode
    OR ISNULL(brd.City,'') <> src.City
    OR ISNULL(brd.Siret,'') <> src.Siret
    OR ISNULL(brd.Ape,'') <> src.Ape
    OR ISNULL(brd.TVAIntra,'') <> src.TVAIntra
    OR ISNULL(brd.TVAIntra,'') <> src.TVAIntra
    OR ISNULL(brd.BankInfos,'') <> src.BankInfos
    OR ISNULL(brd.Capital,'') <> src.Capital
    OR ISNULL(brd.CompanyInfos1,'') <> src.CompanyInfos1
    OR ISNULL(brd.CompanyInfos2,'') <> src.CompanyInfos2
    OR ISNULL(brd.CompanyInfos3,'') <> src.CompanyInfos3
    OR ISNULL(brd.CompanyInfos4,'') <> src.CompanyInfos4
    OR ISNULL(brd.CompanyInfos5,'') <> src.CompanyInfos5
    OR ISNULL(brd.CompanyInfos6,'') <> src.CompanyInfos6
    OR ISNULL(brd.CompanyInfos7,'') <> src.CompanyInfos7
    OR brd.IsDeleted = 1) THEN
UPDATE 
SET 
    [Name] = src.[Name],
    Phone = src.Phone,
    Fax = src.Fax,
    ContactEmail = src.ContactEmail,
    AccountingEmail = src.AccountingEmail,
    Address1 = src.Address1,
    Address2 = src.Address2,
    ZipCode = src.ZipCode,
    City = src.City,
    Siret = src.Siret,
    Ape = src.Ape,
    TVAIntra = src.TVAIntra,
    Capital = src.Capital,
    BankInfos = src.BankInfos,
    CompanyInfos1 = src.CompanyInfos1,
    CompanyInfos2 = src.CompanyInfos2,
    CompanyInfos3 = src.CompanyInfos3,
    CompanyInfos4 = src.CompanyInfos4,
    CompanyInfos5 = src.CompanyInfos5,
    CompanyInfos6 = src.CompanyInfos6,
    CompanyInfos7 = src.CompanyInfos7,
    IsDeleted = 0

WHEN NOT MATCHED BY TARGET THEN 
INSERT 
    ([Code]
    ,[Name]
    ,[Phone]
    ,[Fax]
    ,[ContactEmail]
    ,[AccountingEmail]
    ,[Address1]
    ,[Address2]
    ,[ZipCode]
    ,[City]
    ,[Siret]
    ,[Ape]
    ,[TvaIntra]
    ,[BankInfos]
    ,[Capital]
    ,[CompanyInfos1]
    ,[CompanyInfos2]
    ,[CompanyInfos3]
    ,[CompanyInfos4]
    ,[CompanyInfos5]
    ,[CompanyInfos6]
    ,[CompanyInfos7]
    ,[IsDaughterCompany]
    )
VALUES 
    (src.Code,
    src.[Name],
    src.[Phone],
    src.[Fax],
    src.[ContactEmail],
    src.[AccountingEmail],
    src.[Address1],
    src.[Address2],
    src.[ZipCode],
    src.[City],
    src.[Siret],
    src.[Ape],
    src.[TvaIntra],
    src.[BankInfos],
    src.[Capital],
    src.[CompanyInfos1],
    src.[CompanyInfos2],
    src.[CompanyInfos3],
    src.[CompanyInfos4],
    src.[CompanyInfos5],
    src.[CompanyInfos6],
    src.[CompanyInfos7],
    0
    )

WHEN NOT MATCHED BY SOURCE THEN
UPDATE
SET
    IsDeleted=1;
END

It just blindly checks all fields for a difference. I was also wondering if there was not an issue here.