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

Answers


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.


Need Your Help

Cuda reusing events to determine execution time of multiple sections

c cuda parallel-processing gpu gpgpu

I wish to calculate the execution time for two different sections of my cuda code. In order to do so, I am trying to use CudaEvent_t start, stop.

How to detect Firefox user agent?

browser vbscript asp-classic user-agent

I am working on an application where I am required to make legacy code, which has been designed primarily for Internet Explorer, work with Firefox.