触发器中不允许使用Alter数据库语句

Question

我想在用户要插入数据时动态创建文件组，但是SQL Server会引发异常。

我知道我可以使用SQL Server代理处理此操作，但是如果我的方法不正确，请告诉我正确的方法。

善意。

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

结果：

msg 287，第16级，状态2，过程审核，第24行
触发器中不允许使用Alter数据库语句。

Answer 1

而不是触发器，您可以使用存储的过程进行审核表插入，并在此处包含FileGroup/File/Partition Vaintenance代码。请注意，由于子查询，此触发器将在多排插入物上失败。

也就是说，我认为定期的分区维护每日工作方法更干净。不知道为什么您要为每个分区创建一个新文件和文件组。除非您有特殊的用例，否则您只需将每个分区放在同一文件组上即可。确保分区功能为RANGE RIGHT，以避免SPLIT期间的数据移动和日志记录。