sql - 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.