I am wanting to utilize partitioning based on a
[TenantId] (and later in conjunction with date ranges). Instead of needing to manually insert the latest value within the
PARTITION FUNCTION, I thought of creating a
TRIGGER AFTER INSERT to pull the
[TenantId] value and
ALTER PARTITION FUNCTION to add it to the
SPLIT RANGE. However, I am running into an unexpected error:
Cannot execute ALTER PARTITION FUNCTION on/using table 'Tenant' since the table is the target table or part of cascading actions of a currently executing trigger.
First, I'm creating the
PARTITION FUNCTION [PF_Tenant_Isolation] and
PARTITION SCHEME [PS_Tenant_Isolation] for partitioning on the
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int]) AS RANGE LEFT FOR VALUES (1); GO CREATE PARTITION SCHEME [PS_Tenant_Isolation] AS PARTITION [PF_Tenant_Isolation] ALL TO ([Auth]); GO
Following this, I am creating the
[Tenant] table against the newly created partition scheme.
IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL BEGIN CREATE TABLE [Auth].[Tenant] ( [TenantId] [int] IDENTITY(1,1) ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1 ,[TenantName] [varchar](256) NOT NULL ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC) ) ON [PS_Tenant_Isolation]([TenantId]); END
I seed the first value before creating the trigger.
INSERT INTO [Auth].[Tenant] VALUES (1,'Partition Trigger Test A');
I create the trigger against the [Tenant] table.
CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant] AFTER INSERT AS BEGIN DECLARE @MaxInsertedId int SET @MaxInsertedId = (SELECT MAX([TenantId]) FROM inserted) ALTER PARTITION SCHEME [PS_Tenant_Isolation] NEXT USED [Auth]; ALTER PARTITION FUNCTION [PF_Tenant_Isolation]() SPLIT RANGE (@MaxInsertedId); END
I follow this up with attempting to insert the second
INSERT INTO [Auth].[Tenant] VALUES (1,'Partition Trigger Test B');
This is when the error described above pops up. Based the error itself as well reading the Technet arguments, I understand the issue to be in utilizing
AFTER INSERT. Since the partition action of the transaction relies on utilizing the range value within the partition function, the
ALTER PARTITION SCHEME fails, and thus the entire transaction as well.
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
I have looked into INSTEAD OF INSERT but have not had any success. The trigger fires once and updates the
SPLIT RANGE with a value of 0 (implicitly converted from NULL). I believe this is due to the
IDENTITY not being properly captured in the scope of the transaction.
CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant] INSTEAD OF INSERT AS BEGIN DECLARE @MaxInsertedId int SET @MaxInsertedId = (SELECT [TenantId] FROM inserted) ALTER PARTITION SCHEME [PS_Tenant_Isolation] NEXT USED [Auth]; ALTER PARTITION FUNCTION [PF_Tenant_Isolation]() SPLIT RANGE (@MaxInsertedId); INSERT INTO [Auth].[Tenant] ([TenantActive], [TenantName]) SELECT [TenantActive], [TenantName] FROM inserted; END
Subsequent row inserts into
[Tenant] produce an additional error due to it trying to enter 0 (NULL).
Duplicate range boundary values are not allowed in partition function boundary values list. The boundary value being added is already present at ordinal 1 of the boundary value list.
How can I work around this? Do I need to explicitly set the
IDENTITY value of
[TenantId] in conjunction with
INSTEAD OF INSERT? New inserts into
[Tenant] will be rather sporadic and minimal, but
[TenantId] will be a constraining key across other tables. This is why I decided to investigate this implementation method so to dynamically alter the partition function.