SQL Server Trigger to fill in value based off File Name on Import

Question

I need to create a trigger that fills in a project name for table based off the filename. Currently, I am able to fill in the first project name on import based off the first hostname.

Example after Current import:

Now I need to fill in the rest of the Null Project Names where Project name is Not Null for the corresponding Filename.

Example for Goal Import

How do I get this to work dynamically for each time new data is entered? I also want to make sure it is done by filename to make sure no other sets of data are updated.

Answer 1

Below is an example trigger. This uses HostName as a tie-breaker in the event more than one row exists for the same file with a not-null ProjectName. All rows for the inserted file will be updated to the first non-null ProjectName ordered by HostName.

CREATE TABLE dbo.FooBar (
      FileName varchar(100) NOT NULL
    , ProjectName varchar(100) NULL
    , HostName varchar(100) NOT NULL
      CONSTRAINT PK_FooBar PRIMARY KEY CLUSTERED (FileName, HostName)
    );
GO
CREATE TRIGGER TR_FooBar 
ON dbo.FooBar 
FOR INSERT
AS
UPDATE dbo.FooBar
SET ProjectName = (
    SELECT TOP(1) ProjectName
    FROM dbo.FooBar AS source
    WHERE
        source.FileName = inserted.FileName
        AND source.ProjectName IS NOT NULL
    ORDER BY HostName
    )
FROM inserted
WHERE
    inserted.FileName = FooBar.FileName;
GO