I have a filetable in a SQL Server database, with both T-SQL and Windows Share I/O access enabled.
I have a C# WinForms front end that loads files from this filetable, modifies and updates them back into the filetable. This is all working fine, and I can verify the file contents are getting updated correctly by both the changing file_stream binary as viewed directly in SSMS, and by viewing the files through the Windows Filetable share.
However, the creation_time, last_write_time and last_access_time columns of the filetable are not updated automatically when updating the files using T-SQL.
I've dug through the MSDN pages, which state the columns are 'calculated automatically', but this appears not to be the case.
Updating the files by simply opening them from the share, editing and saving them, does cause the columns to automatically update.
My stored procedure for updating the file_stream binary column is:
ALTER PROCEDURE [dbo].[updateFileBinary]
@fileId uniqueidentifier,
@fileBinary VARBINARY(MAX)
AS
BEGIN
SET NOCOUNT ON;
UPDATE files
SET file_stream = @fileBinary
WHERE stream_id = @fileId;
END
I have seen here that "Describes how Transact-SQL data manipulation language (DML) commands work with FileTables........ Updates can be made to the FILESTREAM data in the file_stream column without affecting any of the other columns, including the timestamps."
That says can be made, but how do we do a T-SQL update and do update those columns?