0

I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating a FileProperties table (especially for non-transactional file copies) is for building relational data that will work with EF6. I'm hopeful exposure here may shed some light on my issue.

I'm using the FileTables feature and have setup a trigger to populate a FileProperties table whenever a file is copied to the SQL Server file share.

I'm trying to grab the file size (cached_file_size), and I get a value of zero. I set up the trigger the same way a standard FileTable does using DATALENTGH(file_stream). The code below is using the inserted tables' file_stream:

ALTER TRIGGER [dbo].[FileTable_Insert_Trigger] 
ON [dbo].[Files]
AFTER INSERT
AS 
BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM INSERTED)
        RETURN;

    DECLARE @s_id UNIQUEIDENTIFIER,
            @fs VARBINARY(MAX),
            @nm NVARCHAR(255),
            @fp NVARCHAR(MAX),
            @cfs BIGINT,
            @ft NVARCHAR(255);
 
    SELECT @s_id = ins.stream_id FROM inserted ins;
    SELECT @fs = ins.file_stream FROM inserted ins;
    SELECT @nm = ins.name FROM inserted ins;
    SELECT @fp = ins.file_stream.GetFileNamespacePath() FROM inserted ins;  
    SELECT @ft = ins.file_type FROM inserted ins;
    SELECT @cfs = DATALENGTH(@fs);

    INSERT INTO [FileProperties] (stream_id, [name], filepath, file_type,
                                  cached_file_size, DateAdded, UserID)
    VALUES (@s_id, @nm, @fp, @ft,
            @cfs, CURRENT_TIMESTAMP, 1);
END

I've also tried grabbing the computed value directly from the FileTable:

SELECT @cfs = Files_1.cached_file_size 
FROM [dbo].Files AS Files_1 
WHERE Files_1.stream_id = @s_id;

Still getting zero. What am I missing here? Thank you.

Note: I tried playing around with getting the DATALENGTH of other values such as the name (@nm) and stream_id (@s_id) and I do get the values correctly.

14
  • The reason no one attempted to answer your question on SO is because your trigger is so fundamentally flawed that no one is going to look beyond that until you fix it. Once you have corrected your trigger such that its correct then people will look at the issues - assuming you still have them.
    – Dale K
    Oct 8, 2021 at 1:57
  • Also as a best practice, don't use obscure variable names such as @nm, @fp etc, that just makes it harder to read. Use full words.
    – Dale K
    Oct 8, 2021 at 2:00
  • And the FileTable row comes with a pre-populated cached_file_size - why not use that?
    – Dale K
    Oct 8, 2021 at 2:05
  • @DaleK - instead of telling me how flawed it is, why not edit and fix it? You did the same thing on SO despite me telling you I'm a novice and this trigger is based off a tutorial I followed. This place is supposed to exist for learning, not criticism. Furthermore, I state the reason I'm doing it in my text - EF6 doesn't support FileTables.
    – merlot
    Oct 8, 2021 at 2:55
  • 1
    Don't confuse terse with personal. None of the advice provided is intended as a criticism, its the fundamental information you need to proceed. If you follow said advice you will learn and your code will be better - which one assumes you want? You were pointed to the correct tutorial on SO which allows you to correct your trigger. Neither site is for people to do it for you - its to provide you with the pointers so you can do it yourself. And once you have removed the obvious issues, people will then be able to focus on the real issues.
    – Dale K
    Oct 8, 2021 at 4:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.