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

sql, sql-server

Solution

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

Problem

Original source