触发从另一个表插入值-MySQL [英] Trigger to insert values from another table - 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.

现在，我在表警报中创建了新行，idRegisto是外键，现在我希望根据idRegisto其他对应表来完成该id。

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.

有人可以帮助我吗？

如果您不理解这个问题，我将尝试进一步阐明。

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

谢谢。

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

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

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

推荐答案

您可以使用插入后触发器

You can use an "after insert trigger"

之后在注册表中插入

并在出现以下情况时使用 New.IdRegistro作为外键

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

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

// 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());

...

我在这里IdRegistro是注册表表的主键（将由您的应用程序自动生成，或通过自动递增生成）。

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

Register->如果值是高/低阈值->触发警报->插入sensores_tem_alarmes

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

这篇关于触发从另一个表插入值-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！