MS SQL FileTable CreatedBy和LastModifiedBy

Question

在使用SQL FileTable表时，我希望为文件(如CreatedBy和LastModifiedBy )存储其他元数据。通过查看SQL文件表模式，默认情况下此信息不可用。

我想知道是否可以通过向表中添加一个触发器来确定这些信息，并使用当前_用户来确定当前用户并将其存储在一个单独的表中？

我探索了SQL FileTable在一个项目中的可行性，并且从未使用过这种技术。

Answer 1

我能够通过使用替代触发器来解决问题。

首先，FileTable有固定的模式，因此不允许新字段。我创建了一个单独的表，用于将创作信息存储在由“path_locator”键控的1到1的关系中。

使用触发器，我能够检测到创作信息并存储它。

在复制、删除和重命名文件时，可以使用suser_sname()检测创作信息(由其创建、修改)。但是，在Word、Excel和记事本中编辑文档时，suser_sname()将返回'dbo‘而不是windows凭据的凭据。我不知道为什么会发生这种情况，但我通过加入sys.dm_filestream_non_transacted_handles从那里获得用户名来解决这个问题。请参阅下面的代码片段：

CREATE OR ALTER TRIGGER Authoring_Update ON dbo.DBADocuments AFTER UPDATE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;

    UPDATE dbo.FileMetadataTable SET ModifiedBy = ISNULL(s.login_name, suser_sname()) 
    FROM dbo.FileMetadataTable f 
    JOIN Inserted i ON i.path_locator = f.PathLocator
    LEFT JOIN sys.dm_filestream_non_transacted_handles s ON f.PathLocator = s.item_id
    LEFT JOIN dbo.FileTable ft WITH (NOLOCK) ON s.item_id = ft.path_locator
END