IDENTITY_INSERT in trigger
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
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.