What's wrong with my Update Trigger for an Sql Server 2012 FileTable

Question

I have a table, 'game.FileAttachments' which has a stream_id column that links to the stream_id column of a File table, 'game.Attachments'. I created an Update trigger on the File table, to update the stream_id on the linked table; reason being, when a file in the File table is modified, the stream_id changes. Here's my trigger; help please!

CREATE TRIGGER game.tr_Update_Attachments
   ON  game.Attachments
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF ( UPDATE(stream_id) )
    BEGIN
        UPDATE game.FileAttachments
            SET stream_id = i.stream_id
            FROM inserted i
            WHERE game.FileAttachments.stream_id IN
            (
                SELECT d.stream_id
                    FROM deleted d INNER JOIN 
                         game.FileAttachments f ON f.stream_id = d.stream_id
            )
    END
END

also tried this:

IF ( UPDATE(stream_id) )
BEGIN
    UPDATE game.FileAttachments
        SET stream_id = i.stream_id
        FROM inserted i INNER JOIN
             deleted d ON 1 = 1 INNER JOIN 
             game.FileAttachments f ON f.stream_id = d.stream_id
END

But that also does not work.

Ok, I created a Delete trigger to test a theory; the FileTable record associated with the file I am modifying is NOT updated, but is instead deleted, and a totally new record created. Well, turns out, for an Ms Word doc, this is true. But, created a plain text file, and I can update as many times as I want, and the stream_id never changes. So, Microsoft Word, the application, it seems, clones the original document, giving it a temp name, then, when a user chooses to save it, the original is simply deleted, and the clone renamed the same as the original. That BYTES !

Answer 1

I think your trigger definition needs to have this:

CREATE TRIGGER game.tr_Update_Attachments
ON  game.Attachments
AFTER UPDATE, DELETE

as you refer to:

SELECT d.stream_id
                FROM deleted d INNER JOIN 
                     game.FileAttachments f ON f.stream_id = d.stream_id

in your where clause...either that or you incorrectly refer to this table and need to capture the "deleted" attachments into a temp table?