SQL Server 2014 : FileTable trigger on update File_Stream.PathName()
<p>I have a FileTable <code>FT</code> and another table <code>AT</code>. In <code>AT</code>, I have extended metadata properties of the file in <code>FT</code>. </p>

<p>I have tried to create a trigger <code>ON FT FOR UPDATE</code> that will update the file path that is in <code>AT</code>. </p>

<p>Here is what I've tried:</p>

<pre><code>ALTER TRIGGER [dbo].[u_filepath] 
ON [FileDB].[dbo].[FT] 
FOR UPDATE
AS
    Declare @newpath nvarchar(1000)
    Declare @oldpath nvarchar(1000)

    select @newpath = file_stream.pathname()
    from inserted

    select @oldpath = file_stream.pathname()
    from deleted

    update AT
    set [Path] = @newpath
    where [Path] = @oldpath
GO
</code></pre>

<p>When I execute the query, it spins. I'm planning on leaving it running overnight just in case it decides to do something.</p>

<p>I want the <code>Path</code> column in <code>AT</code> to update to the updated <code>file_stream.PathName()</code> from <code>FT</code>.</p>

<p>Is the trigger logical? </p>

<p>Should I store the file_stream BLOB in my AT Path column instead of the actual path?</p>