When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by default.
I am wondering if it’s possible to determine such information by for example adding a trigger to the table and use current_user to determine current user and store it in a separate table?
I exploring the feasibility of SQL FileTable for a project and have never used this tech.
I was able to solve the problem by using instead triggers.
First, a FileTable has a fixed schema therefore new fields are not allowed. I’ve created a separate table to store authoring information in a 1-to-1 relationship keyed by ‘path_locator’.
Using triggers, I was able to detect authoring information and store it.
When copying, deleting and renaming files, the authoring information (created by, modified by) can be detected by using suser_sname(). However when editing the document in Word, Excel, Notepad, there are scenarios where the suser_sname() will return ‘dbo’ instead of the credentials of windows credentials. I am not sure why this happens but I worked around it by joining to sys.dm_filestream_non_transacted_handles to get username from there. See code snippet below:
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