表“X"的 IDENTITY_INSERT 已经打开.无法对表“Y"执行 SET 操作 [英] IDENTITY_INSERT is already ON for table 'X'. Cannot perform SET operation for table 'Y'

问题描述

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

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:

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

"At any time, only one table in a session can have the IDENTITY_INSERT property set to 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

但由于数据是通过触发器填充的，因此无法这样做.

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

请问有人能解决我的问题吗?

Does anyone have a solution to my problem please?

我道歉.

谢谢大家.

触发-----

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

数据库----

推荐答案

允许 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 值.

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.

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

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

这篇关于表“X"的 IDENTITY_INSERT 已经打开.无法对表“Y"执行 SET 操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！