The ALTER DATABASE statement is not allowed within a trigger

Question

I want to create the file group dynamically when user want to insert data into the table, but SQL Server throws an exception.

I know that I can handle this with SQL Server Agent, but if my approach isn't correct please tell me the correct way.

Kind regards.

ALTER TRIGGER [AuditTrigger]
ON [Audit]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @DateInserted DATETIME = (SELECT DateInserted FROM inserted);
    DECLARE @NextRange DATETIME;

    DECLARE @currentFileGroup NVARCHAR(MAX)= ('APP_PT_' + CAST(YEAR(@DateInserted) AS NVARCHAR(4)) +'_'+ CAST(MONTH(@DateInserted) AS NVARCHAR(2)))
    --print @currentFileGroup;

    DECLARE @fileExsits BIT = (SELECT (CASE WHEN EXISTS(SELECT NULL AS [EMPTY]  FROM SYS.FILEGROUPS WHERE name  LIKE  @currentFileGroup) THEN 1  ELSE 0  END))

    IF @fileExsits = 0
    BEGIN 
        SET @NextRange = (SELECT Replace(CONVERT(VARCHAR(10), @DateInserted, 111),'/','-'))

        DECLARE @filefullname VARCHAR(MAX) = (SELECT physical_name FROM SYS.DATABASE_FILES WHERE name = 'DB_Test')
        DECLARE @fgFullName  VARCHAR(MAX) = (SELECT (LEFT(@filefullname, LEN(@filefullname) - CHARINDEX('\', REVERSE(@filefullname))) + '.ndf'))

        -- The exception occurs here --
        ALTER DATABASE DB_TEST 
        ADD FILE (NAME = [@currentFileGroup],
                  FILENAME = [@fgFullName],
                  SIZE = 5MB,
                  MAXSIZE = 100MB,
                  FILEGROWTH = 1MB)
        TO FILEGROUP Audit_2017

        ALTER PARTITION FUNCTION  [PF]() 
        SPLIT RANGE (@NextRange);

        ALTER PARTITION SCHEME [PS]  
        NEXT USED [@currentFileGroup];
    END

    INSERT INTO LogTable VALUES (@currentFileGroup)

    INSERT INTO [Audit] 
        SELECT DateInserted, Title 
        FROM inserted;
END

Result:

Msg 287, Level 16, State 2, Procedure AuditTrigger, Line 24
The ALTER DATABASE statement is not allowed within a trigger.

A trigger should be **very nimble and fast** - it should **NOT** do any extensive calculations or processing. Altering a database and adding a new file is **way too extensive** an operation to handle inside a trigger! Don't do this - even if you could! You need to rethink your strategy here - this is definitely not the way to go — marc_s, Dec 17 '17 at 13:39

score 2 · Accepted Answer · answered Dec 17 '17 at 13:19

Instead of a trigger, you could use a stored procedure for the Audit table inserts and include the filegroup/file/partition maintenance code there. Note that this trigger will fail on multi-row inserts due to the subquery.

That said, I think the scheduled daily job approach for partition maintenance is cleaner. Not sure why you are bothering to create a new file and filegroup for each partition. Unless you have a special use case, you could simply place each partition on the same filegroup. Make sure the partition function is RANGE RIGHT to avoid excessive data movement and logging during SPLIT.

The ALTER DATABASE statement is not allowed within a trigger

1 Answers1