触发器从另一个表- Mysql中插入值。

I did this to trigger when the temperature is higher or lower than the parameter set off an alarm. Ie automatically populates the fields of the table alarm.

当温度高于或低于警报参数时，我就会触发它。Ie自动填充表报警器的字段。

Now I created a new row in the table alarms, the idRegisto which is foreign key, now I want that id be completed in accordance with the idRegisto other corresponding table.

现在我在表报警器中创建了一个新的行，idRegisto是外键，现在我想要根据idRegisto其他相应的表来完成这个id。

Does anyone can help me please?

有人能帮我吗?

If you are not understanding the question I will try to clarify further.

如果你不理解这个问题，我会试着进一步阐明。

Thank you.

谢谢你！

------------TRIGGER-------------------

触发- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

DELIMITER $$
create TRIGGER alerta
BEFORE INSERT ON registos
FOR EACH ROW
begin
Set @tempmax=0;
Set @tempmin=0;
Set @hummax=0;


select lim_inf_temp, lim_sup_temp into @tempmin, @tempmax from sensores  where idSensor=NEW.idSensor;


Set @maxidAlarme=0;
if (CAST(NEW.Temperatura AS UNSIGNED)<@tempmin) then
SELECT MAX(idAlarme) into @maxidAlarme FROM alarmes;
SET @maxidAlarme=@maxidAlarme+1;
INSERT INTO alarmes(idAlarme,descricao_alarme) VALUES (@maxidAlarme,"high-temperature");
INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme) VALUES     (NEW.idSensor,@maxidAlarme,NOW());
end if; 


if (CAST(NEW.Temperatura AS UNSIGNED)>@tempmax) then
SELECT MAX(idAlarme) into @maxidAlarme FROM alarmes;
SET @maxidAlarme=@maxidAlarme+1;
INSERT INTO alarmes(idAlarme,descricao_alarme) VALUES (@maxidAlarme,"lower temperature");
INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme) VALUES (NEW.idSensor,@maxidAlarme,NOW());
end if; 
DELIMITER  ;

------------ER------------------------

- - - - - - - - - - - -呃- - - - - - - - - - - - - - - - - - - - - - - -

1 个解决方案

#1

You can use an "after insert trigger"

你可以使用"插入触发器"

AFTER INSERT ON registos

插入后registos

And take the "New.IdRegistro" to use as foreign key when populating the alarmes table.

“新。在填充阿拉木图时使用“作为外键”。

//EDIT: using your code:

/ /编辑:使用您的代码:

AFTER INSERT ON registos
FOR EACH ROW
begin
Set @tempmax=0;
Set @tempmin=0;
Set @hummax=0;

...

…

INSERT INTO alarmes(idAlarme,descricao_alarme,idRegistro) VALUES (@maxidAlarme,"lower temperature",New.IdRegistro);

INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme) VALUES (NEW.idSensor,@maxidAlarme,NOW());

...

…

I assume here that IdRegistro is a primary key (that will be autogenarated by your application or via autoincrement) of the registro table.

我假设IdRegistro是一个主键(它将由您的应用程序或通过autoincrement自动生成)的registro表。

Register->if value higher/lower threshold -> trigger alarm -> insert sensores_tem_alarmes

寄存器->如果值高/低阈值->触发报警->插入sensores_tem_alarmes。