Triggers on FileTables in SQL 2012

This is a follow up post to the below which explained on step by step details for configuring and implementing FileTables in SQL 2012

In this post, I will be illustrating with sample code how triggers can be implented on a FileTable
Now that we've created the FileTable as per above blog, we can proceed and create an AFTER trigger in it.
Few things to note regarding triggers on FileTables are

• You cant have INSTEAD OF triggers on a FileTable
• You cant modify parent or any other FileTable inside a trigger

Now lets go ahead and create the trigger on table. For the purpose this blog, I'm considering a simple scenario of logging details of image file manipulations on a FileTable using a trigger

We will create a table like below to log the image file manipulation on FileTable

CREATE TABLE ImageLog
(
ID int IDENTITY(1,1) NOT NULL,
ImageName varchar(100),
ImagePath varchar(max),
Inserteddate datetimeoffset,
[Action] varchar(30)
)

The structure of table is self explanatory. Now lets go ahead and create trigger to populate this.
The trigger code is as below

CREATE TRIGGER MyFileTableTrigger
ON MyFileTable
AFTER INSERT,UPDATE,DELETE
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO ImageLog
(
     ImageName,
     ImagePath,
     Inserteddate,
     [Action]
)
SELECT COALESCE(i.name,d.name) ,
       COALESCE(i.file_stream.GetFileNamespacePath(),'deleted'),
       COALESCE(i.creation_time,d.creation_time),
       CASE WHEN i.name IS NULL THEN 'DELETE'
            WHEN d.name IS NULL THEN 'INSERT'
            ELSE 'UPDATE'
       END
FROM INSERTED i
FULL JOIN DELETED d
ON d.stream_id = i.stream_id
AND d.file_type = i.file_type
WHERE COALESCE(i.file_type,d.file_type) IN ('jpg','bmp','png')
END
GO

　
One thing to note here is that triggers created on FileTables can capture modifications happening to FileSystem though filesystem access as well


Now lets go ahead and copy,delete and add some files to FileTable. I've added,copied and deleted few files through filesystem and this is how the log table looks after that

As you see from the above the log table will capture details of all image file manipulations happening on FileTable be it through t-sql query or through filesystem operations. The reason you see multiple entries for some files is because of the number of internal file manipulations involved in some operation. For example creating new file and renaming involves INSERT followed by UPDATE action.
One final point to note here is the use of function Getfilenamespacepath() in trigger. This function returns the UNC file path.
http://msdn.microsoft.com/en-us/library/ff929166.aspx

Though you can use it in trigger code by fetching path value from INSERTED table, you cant have it fetching path from DELETED table as you'll get below error

Msg 33427, Level 16, State 1, Procedure MyFileTableTrigger, Line 16

Function GetFileNamespacePath is not allowed on the deleted table inside a trigger.