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.
