IDENTITY_INSERT is already ON for table 'X'. Cannot perform SET operation for table 'Y'

Question

I created a trigger that performs a check and automatically populates data into 2 tables. Only what happens the following error :

IDENTITY_INSERT is already ON for table 'X'. Cannot perform SET operation for table 'Y'. 

I found this while researching the error:

"At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.”

So the fix was easy:

SET IDENTITY_INSERT Table1 ON -- insert statements for table1 SET IDENTITY_INSERT Table1 OFF  SET IDENTITY_INSERT Table2 ON -- insert statements for table2 SET IDENTITY_INSERT Table2 OFF  SET IDENTITY_INSERT Table3 ON -- insert statements for table3 SET IDENTITY_INSERT Table3 OFF 

But as the data is populated via trigger is not possible to do so.

Does anyone have a solution to my problem please?

I apologize.

Thank you all.

Trigger-----

CREATE TRIGGER Alert ON registos AFTER INSERT AS BEGIN  DECLARE @comp decimal = 0 DECLARE @id_sensores_em_alerta decimal DECLARE @tempmin decimal = 0 DECLARE @current_max_idAlarme int = (SELECT MAX(IdAlarme) FROM alarmes) DECLARE @maxidAlarme int DECLARE @temp decimal = (SELECT s.lim_inf_temp  from sensores s JOIN inserted i ON s.idSensor=i.idSensor )   -- Insert into alarmes from the inserted rows if temperature less than tempmin INSERT alarmes (IdAlarme, descricao_alarme,data_criacao, idRegisto)     SELECT     ROW_NUMBER() OVER (ORDER BY i.idRegisto) + @current_max_idAlarme, 'temp Error', GETDATE(), i.idRegisto     FROM inserted AS i     WHERE i.Temperatura < @temp  SET @maxidAlarme = (SELECT MAX(IdAlarme) FROM alarmes)  INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme)  SELECT i.idSensor, @maxidAlarme, GETDATE() FROM inserted i SET @comp += 1;   SET @id_sensores_em_alerta=1;  SET  @id_sensores_em_alerta = (SELECT MAX(id_sensores_em_alerta)  FROM sensores_em_alerta)  INSERT INTO sensores_em_alerta(id_sensores_em_alerta, idSensor, idAlarme, data_registo, numerosensoresdisparados)  SELECT @id_sensores_em_alerta, i.idSensor, @maxidAlarme, GETDATE(), @comp FROM inserted i end 

DataBase----

Answer 1

I had a similar problem but it did not involve table triggers. I was running a script that refreshes data for multiple tables and I hit a foreign key reference error.

According to MSDN:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

To resolve this, I ran SET IDENTITY_INSERT [dbo].[table_name] OFF for each table I was trying to insert into. Then I was able to refresh my tables again after I corrected the reference error.

Edit: I should also mention that you can just disconnect and then reconnect to reset your session.

Answer 2

Allow SQL Server to insert the identity values automatically for you. Since this is a trigger, there could multiple rows being inserted at a time. For one row inserts, you can use SCOPE_IDENTITY() function (http://msdn.microsoft.com/en-us/library/ms190315.aspx) to retrieve the identity value of your last inserted row. However, since we could have multiple rows inserted in a trigger, we will use the OUTPUT clause (http://msdn.microsoft.com/en-us/library/ms177564.aspx) to get back a list of the inserted IdAlarme values for each idRegisto.

I'm assuming that alarmes.IdAlarme and sensores_em_alerta.id_sensores_em_alerta are the two identity fields in this trigger. If that is the case, then this should work:

CREATE TRIGGER Alert ON registos AFTER INSERT AS BEGIN  DECLARE @comp decimal = 0 DECLARE @id_sensores_em_alerta decimal DECLARE @tempmin decimal = 0 DECLARE @temp decimal = (SELECT s.lim_inf_temp  from sensores s JOIN inserted i ON s.idSensor=i.idSensor )  DECLARE @tblIdAlarme TABLE (idRegisto int not null, IdAlarme int not null);  -- Insert into alarmes from the inserted rows if temperature less than tempmin --  IdAlarme is identity field, so allow SQL Server to insert values automatically. --      The new IdAlarme values are retrieved using the OUTPUT clause http://msdn.microsoft.com/en-us/library/ms177564.aspx INSERT alarmes (descricao_alarme,data_criacao, idRegisto) OUTPUT inserted.idRegisto, inserted.IdAlarme INTO @tblIdAlarme(idRegisto, IdAlarme)     SELECT descricao_alarme = 'temp Error', data_criacao = GETDATE(), i.idRegisto     FROM inserted AS i     WHERE i.Temperatura < @temp ;  --It looks like this table needs a PK on both idSensor and idAlarme fields, or else you will get an error here  --  if an alarm already exists for this idSensor. INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme)  SELECT i.idSensor, a.IdAlarme, dataAlarme = GETDATE() FROM inserted i INNER JOIN @tblIdAlarme a ON i.idRegisto = a.idRegisto ;  --not sure what this is doing?? Will always be 1. SET @comp += 1;  --id_sensores_em_alerta is an identity field, so allow SQL Server to insert values automatically INSERT INTO sensores_em_alerta(idSensor, idAlarme, data_registo, numerosensoresdisparados)  SELECT i.idSensor, a.IdAlarme, data_registo = GETDATE(), numerosensoresdisparados = @comp FROM inserted i INNER JOIN @tblIdAlarme a ON i.idRegisto = a.idRegisto ;  END