Solved: I have update statements for the same table inside the body of the INSERT trigger. Nested triggers are disabled on my database on SQL Server 2012 but after the restore on SQL Server 2016, the setting was somehow enabled.
Edit: It appears that the 'nested triggers' setting of the SQL Server instance was set to 'False' on SQL12 and to 'True' on SQL16.
The cryptic error in the AFTER
trigger is due to performing a DDL against the trigger target table. With the INSTEAD OF
trigger, you would need to perform the INSERT
to get the assigned IDENTITY
value and then split the partition function. However, you probably don't want to use IDENTITY here anyway since those may have gaps that are sometimes large and result in an untidy partition boundary list.
Below is an example that ditches the IDENTITY and uses a RANGE RIGHT function, which I believe is more natural for incremental partition boundaries. This version validates exactly one row is inserted but could be extended to handle multi-row inserts if needed. Your use case as I understand it suggests only rare singleton inserts.
--start with no partition boundaries
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
AS RANGE RIGHT FOR VALUES ();
GO
CREATE PARTITION SCHEME [PS_Tenant_Isolation]
AS PARTITION [PF_Tenant_Isolation]
ALL TO ([Auth]);
GO
CREATE TABLE [Auth].[Tenant] (
[TenantId] [int] NOT NULL
,[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]);
GO
CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
DECLARE @TenantId int;
BEGIN TRY
--Get next TenantId and exclusively lock table to prevent deadlocking during DDL.
--If other tables are partitoned via this function, add code to get exclusive locks on those too.
SELECT TOP(1) @TenantId = COALESCE(MAX(TenantId),0) + 1 FROM [Auth].[Tenant] WITH(TABLOCKX);
INSERT INTO [Auth].[Tenant] ([TenantId], [TenantActive], [TenantName])
SELECT @TenantId, [TenantActive], [TenantName]
FROM inserted;
IF @@ROWCOUNT <> 1
BEGIN
RAISERROR('Exactly one row must be inserted into Auth.Tenant at a time',16,1);
END;
ALTER PARTITION SCHEME [PS_Tenant_Isolation]
NEXT USED [Auth];
ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
SPLIT RANGE (@TenantId);
END TRY
BEGIN CATCH;
THROW;
END CATCH;
GO
INSERT INTO [Auth].[Tenant]([TenantActive], [TenantName])
VALUES (1,'Partition Trigger Test A');
GO
EDIT:
I see your notation, but given that queries will read from [Tenant],
wouldn't the opposite happen where this would actually cause
deadlocks?
The course-grained X lock on the Tenant table will wait for (be blocked by) other concurrent activity against the table to complete and, once granted, block other activity against the table. This blocking will prevent deadlocks on the Tenant table during the DDL operation within the trigger transaction. The duration of the SPLIT itself will be fast since rows are not moved between partitions. The duration of blocking before the initial block X lock is granted will depend on how long the other queries run.
In the case of multiple tables (i.e. related tables partitioned by schemes based on the same function), deadlocks can still occur if the locking order in the trigger is different than that of other activity. An exclusive lock on those tables too in the trigger can only mitigate the likelihood of deadlocks in that case. For example, if you have a SELECT query that joins Tenant and TenantDetails, both partitioned similarly, a deadlock may occur if the query acquires locks those tables in the reverse order as the trigger.
Also, I understand with partition schemes you typically want to leave
partitions on the left and right boundaries that are "empty" for
proper switching.
Empty partitions are a consideration for SPLIT
and MERGE
but not SWITCH
. With the SPLIT
in the trigger, the split partition is always empty so no expensive data movement is needed to conform to the new boundary specification.
The general best practice is to MERGE
boundaries when both adjacent partitions are empty. That said, you can sill MERGE
without row movement as long as the partition containing the boundary (one on the right with a RANGE RIGHT
function) is empty.
Best Answer
Permissions on indirectly referenced objects are not checked as long as the objects involved have the same owner. This is known as ownership chaining. Users need permission on only the object used directly (table in this case), not on the objects used by the trigger (e.g. function) as long as the ownership chain is unbroken.
One can leverage ownership chains to encapsulate functionality in views, stored procedures, triggers, and functions without granting direct permissions.
See the Ownership Chains topic in the Authorization and Permissions in SQL Server documentation for more information.