0

I have 2 tables, "TableA" and "TableB" setup like below.

Table A

+----------+------+---------------+
| StreamID | Name | Other Columns |
+----------+------+---------------+

Table B

+----+--------------+------------------------+-----------+
| ID | FileStreamID | AdditionalFileStreamID | DateAdded |
+----+--------------+------------------------+-----------+

Both FileStreamID and AdditionalFileStreamID in Table A are foreign key constraints to the StreamID column in Table B. I tried setting up the constraint on both columns so that when the row in Table A is deleted, the 2 columns are both set to NULL, however got a 'multiple cascade paths' exception (similar issue below):

2 foreign key to the same table may cause cycles or multiple cascade paths

Instead, I tried creating the following trigger to handle it:

CREATE TRIGGER dbo.File_Deletion_Trigger
    ON dbo.TableA
    INSTEAD OF DELETE
AS BEGIN
    UPDATE dbo.TableB
    SET FileStreamID = NULL
    WHERE FileStreamID IN (SELECT d.StreamID FROM deleted d)

    UPDATE dbo.TableB
    SET ScheduleStreamID = NULL
    WHERE AdditionalFileStreamID IN (SELECT d.StreamID FROM deleted d)

    DELETE FROM  dbo.TableA
    WHERE   StreamID IN (SELECT d.StreamID FROM deleted d)
END
GO

I am getting the following exception when I try to create the trigger:

Msg 195, Level 15, State 10, Procedure TableA, Line 1 [Batch Start Line 0]
'getfileextension' is not a recognized built-in function name.
Msg 474, Level 16, State 0, Procedure File_Deletion_Trigger, Line 6 [Batch Start Line 0]
Unable to load the computed column definitions for table "deleted".

Does anyone know why this might be happening?

Drogan41
  • 51
  • 5
  • 5
    That error isn't from that trigger. That suggests you have other triggers, or other SQL you aren't showing us. – Thom A Feb 24 '20 at 09:48
  • 2
    From the error message text it seems like you have a problem with a computed column on `TableA` - one that uses a user defined function called `getfileextension` that doesn't exists anymore. – Zohar Peled Feb 24 '20 at 10:50
  • That clears it up a little. I'll have a look at the table and see if I can find a solution. Thanks for the guidance! – Drogan41 Feb 24 '20 at 12:04
  • I think I have found where the issue lies. My TableA is an SQL file table and has a column called 'file_type'. This is a computed column with the computed text '(getfileextension([name]))'. – Drogan41 Feb 24 '20 at 12:15
  • and it looks like file tables do not support 'INSTEAD OF' triggers. I might have to find a different way of implementing this. Cheers! https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetable-compatibility-with-other-sql-server-features?view=sql-server-ver15 – Drogan41 Feb 24 '20 at 12:17

1 Answers1

0

My TableA is a file table and file tables do not support 'INSTEAD OF' triggers.

Therefore I have changed my trigger to the following and it seems to work:

CREATE TRIGGER dbo.File_Deletion_Trigger
    ON dbo.TableA
    FOR DELETE
AS BEGIN
    UPDATE dbo.TableB
    SET FileStreamID = NULL
    WHERE FileStreamID IN (SELECT d.StreamID FROM deleted d);

    UPDATE dbo.TableB
    SET AdditionalFileStreamID = NULL
    WHERE AdditionalFileStreamID IN (SELECT d.StreamID FROM deleted d);
END
GO
Drogan41
  • 51
  • 5