How to cope with IDENTITY_INSERT for an INSTEAD OF trigger on a table with an IDENTITY column?

Question

I have a table with an IDENTITY column, and I have an INSTEAD OF trigger on this table. If IDENTITY_INSERT got turned on, I would want to insert the value being manually specified for the IDENTITY column.

Is there any way to properly cope with this scenario, such as detecting the value of IDENTITY_INSERT? From reading, it looks like detecting the current value of IDENTITY_INSERT for a specific table has been impossible in the past. I don't know if it's possible with newer versions of SQL Server.

Table creation SQL:

CREATE TABLE [TestTable]
(
    [Id]            INTEGER IDENTITY    NOT NULL    PRIMARY KEY,
    [ExampleField]  BIT                 NOT NULL    DEFAULT(1)
)

This is what I've currently tried, but it seems rather wasteful as IDENTITY_INSERT is likely to be off for most of the time, meaning that it's always going to be failing on the first insert attempt, which seems wasteful, performance-wise.

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT
AS
BEGIN
    BEGIN TRY
        INSERT INTO [TestTable]([Id],[ExampleField])
        SELECT [Id], [ExampleField]
        FROM [inserted]
    END TRY
    BEGIN CATCH
        INSERT INTO [TestTable]([ExampleField])
        SELECT [ExampleField]
        FROM [inserted]
    END CATCH
END

Answer 1

If your IDENTITY seed and increment is such that the generated value will always be non-zero (positive seed and increment or negative seed and increment), you can check for non-zero values in the virtual inserted table and use that value when present. This relies on my observation that the identity value is zero with an INSTEAD OF trigger and IDENTITY_INSERT OFF. However, I could not find this behavior specifically documented so you should vet in your environment and use at your own risk.

Example:

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM inserted WHERE ID <> 0)
BEGIN
    --IDENTITY_INSERT is ON
    INSERT INTO [TestTable]([Id],[ExampleField])
    SELECT [Id], [ExampleField]
    FROM [inserted];
END
ELSE
BEGIN
    --IDENTITY_INSERT is OFF
    INSERT INTO [TestTable]([ExampleField])
    SELECT [ExampleField]
    FROM [inserted];
END;
GO

SET IDENTITY_INSERT dbo.TestTable OFF;
GO
--identity value auto-assigned
INSERT INTO TestTable VALUES(1);
GO

SET IDENTITY_INSERT dbo.TestTable ON;
GO
--explict identity value specified
INSERT INTO TestTable(ID, ExampleField) VALUES(11, 1);
GO

SET IDENTITY_INSERT dbo.TestTable OFF;
GO
--fails as expected because identity value cannot be specified with IDENTITY_INSERT OFF
INSERT INTO TestTable(ID, ExampleField) VALUES(11, 1);
GO

SET IDENTITY_INSERT dbo.TestTable ON;
GO
--fails as expected because identity value must be specified with IDENTITY_INSERT ON
INSERT INTO TestTable VALUES(1);
GO