[SOLVED] The ALTER DATABASE statement is not allowed within a trigger

The ALTER DATABASE statement is not allowed within a trigger

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.

Solution

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.