SQL 表“X”的 IDENTITY_INSERT 已经打开。无法对表“Y”执行 SET 操作

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

提问by rpires_098

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

我创建了一个触发器，它执行检查并自动将数据填充到 2 个表中。只会发生以下错误：

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

“在任何时候，一个会话中只有一个表可以将 IDENTITY_INSERT 属性设置为 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----

数据库 - -

采纳答案by BateTech

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 OUTPUTclause (http://msdn.microsoft.com/en-us/library/ms177564.aspx) to get back a list of the inserted IdAlarmevalues for each idRegisto.

允许 SQL Server 自动为您插入标识值。由于这是一个触发器，一次可以插入多行。对于一行插入，您可以使用SCOPE_IDENTITY()函数 ( http://msdn.microsoft.com/en-us/library/ms190315.aspx) 来检索最后插入行的标识值。但是，由于我们可以在触发器中插入多行，我们将使用OUTPUT子句 ( http://msdn.microsoft.com/en-us/library/ms177564.aspx) 来获取IdAlarme每个插入值的列表idRegisto.

I'm assuming that alarmes.IdAlarmeand sensores_em_alerta.id_sensores_em_alertaare the two identity fields in this trigger. If that is the case, then this should work:

我假设alarmes.IdAlarme和sensores_em_alerta.id_sensores_em_alerta是此触发器中的两个身份字段。如果是这种情况，那么这应该有效：

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

回答by fujiiface

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:

根据MSDN：

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

在任何时候，会话中只有一个表可以将 IDENTITY_INSERT 属性设置为 ON。

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

为了解决这个问题，我运行SET IDENTITY_INSERT [dbo].[table_name] OFF了我试图插入的每个表。然后，在更正参考错误后，我能够再次刷新我的表格。

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

编辑：我还应该提到您可以断开连接然后重新连接以重置您的会话。