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

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

Home Forums Partitioning Partitioning [SOLVED]: The ALTER DATABASE statement is not allowed within a trigger

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #36110

    Anonymous

    QuestionQuestion

    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.

    #36111

    Anonymous

    Accepted AnswerAnswer

    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.

    Source: https://stackoverflow.com/questions/47853854/the-alter-database-statement-is-not-allowed-within-a-trigger
    Author: Dan Guzman
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.