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 !

Has the question changed due to your delete trigger test? Is it now "Why does my Update Trigger for an Sql Server 2012 FileTable work except for Microsoft Word documents?" Or is that question also answered to your satisfaction (i.e. MS Word clones and deletes)? — peterk411, Nov 15 '13 at 18:56

score 0 · Answer 1 · answered Aug 29 '14 at 13:35

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?

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

1 Answers1