SQL Server触发器在导入时根据文件名填写值 [英] SQL Server Trigger to fill in value based off File Name on Import

问题描述

我需要创建一个触发器，该触发器根据文件名填充表的项目名称。目前，我能够根据第一个主机名填写导入时的第一个项目名。

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.

现在我需要填写其余的Null项目名称，其中对应文件名的Project名称不是Not Null。

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

每次输入新数据时，如何使它动态工作？
我也想确保它是通过文件名完成的，以确保没有其他数据集被更新。

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.

推荐答案

以下是示例触发器。如果同一文件的ProjectName不为空，则使用HostName作为平局。插入文件的所有行将更新为HostName排序的第一个非空ProjectName。

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

这篇关于SQL Server触发器在导入时根据文件名填写值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！