1. Alan

    Thanks for the pointer on this annoying “feature”. I refined your approach a bit to do away with the real table and set identity by doing the following at the start of the Trigger
    DECLARE @SQL nvarchar(200)
    SET @SQL = ‘DECLARE @insert_id_fix TABLE ([dummy] int,[id] [bigint] IDENTITY(‘ + cast(@@IDENTITY as varchar(50)) + ‘,1) NOT NULL);
    INSERT INTO @insert_id_fix (dummy) VALUES(1);’

    and then at the end doing an
    Exec sp_executesql @SQL

    Slightly neater I felt given the SET IDENTITY_INSERT limitations


Leave a Reply

Your email address will not be published. Required fields are marked *