表格'X'的IDENTITY_INSERT已经开启。无法为表格'Y'执行SET操作。

Question

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

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

我在调查错误时发现了这个内容：

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

修复方法很简单：

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

但是由于数据是通过触发器填充的，所以无法这样做。

请问有人有解决我的问题的方法吗？

我很抱歉。

谢谢大家。

触发器-----

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

数据库----

- strange_098

---

你的触发器长什么样子？ - podiluska

在你的触发器中设置这些Identity insert开/关。 - M.Ali

如果表格总是从触发器中填充，为什么还要在它们上面有标识字段呢？ - HLGEM

乍一看，如果您有太多要执行的“标识插入”，那么听起来似乎您有太多的标识列...？ - Philip Kelley

看起来你只是想在“identity”列中插入下一个连续值。这正确吗？ - BateTech

Answer 1

我遇到了类似的问题，但它没有涉及表触发器。 我正在运行一个刷新多个表数据的脚本，然后出现了外键引用错误。

根据MSDN：

 

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

要解决此问题，我为我试图插入数据的每个表运行了SET IDENTITY_INSERT [dbo].[table_name] OFF。 然后，在我纠正了参照错误之后，我可以再次刷新我的表。

编辑：我还应该提到，您只需断开连接，然后重新连接即可重置会话。

- fujiiface

---

太棒了，兄弟。我为了解决这个问题已经挣扎了很长时间。10+ - Aravin

支持 @E-A 的建议，我在 SSMS 中打开了多个选项卡。一旦我右键单击并断开了所有连接（包括对象资源管理器），关闭了所有会话，我重新连接后查询就可以工作了。 - suspicious_williams

在SSMS的新查询选项卡中执行它就可以了。 - KOmrAD

这应该是答案。 - Francisco Aguilera

谢谢你提醒我这是一个会话级别的设置，而不是表参数。在设置了插入操作后，我从表中删除了标识列，但无法关闭它！我觉得很荒谬，竟然要添加一个标识列才能关闭插入操作。断开/重新连接解决了问题！ - Brien Malone

Answer 2

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

我假设在此触发器中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

- BateTech