What happened to my update trigger for SQL Server 2012 Sql Server file

Question

I have a table "game.FileAttachments" that has a stream_id column that references the stream_id column of the file table "game.Attachments". I created an update trigger in the file table to update stream_id in the linked table; the reason is that when a file in the file table changes, stream_id changes. Here is my trigger; help me 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 this also does not work.

Ok, I created a Delete trigger to test the theory; the FileTable entry associated with the file I am editing is NOT updated, but instead a completely new entry is deleted. Well, it turns out that this is true for an Ms Word document. But, created a simple text file, and I can update as many times as I want, and stream_id never changes. Thus, Microsoft Word, it seems, the application clones the source document, giving it a temporary name, and then, when the user decides to save it, the original is simply deleted, and the clone is renamed in the same way as the original. This is BYTES!

Answer 1

I think your trigger definition should have the following:

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

How do you see:

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

in your where clause ... is it either this or you are referencing this table incorrectly and should capture the "remote" attachments to the temporary table?