IDENTITY_INSERT in trigger

Question

Is it possible to turn it on in a table trigger?

I tried creating a trigger with SET IDENTITY_INSERT tableName ON

But when I open the trigger definition, I see that the statement is not there....

This is my query to alter my trigger to add the IDENTITY_INSERT, when I open the definition, the IDENTITY_INSERT is removed ...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET IDENTITY_INSERT dbo.MyTable ON
GO

ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[MyTable]
AFTER INSERT AS



INSERT INTO MyOtherTable
(   
    // values
)


SET IDENTITY_INSERT dbo.MyTable OFF

Answer 1

To turn it on and off in the trigger, your SET IDENTITY_INSERT ON needs to be here :

AFTER INSERT AS

SET IDENTITY_INSERT dbo.MyTable ON

INSERT INTO MyOtherTable

rather than before the trigger definition begins. In its current location it's only on when the trigger is being created, not when it's actually being utilised.