MYSQL 从另一个表插入值 - 触发器

Question

我的数据库中有这些表:

我想添加 registod 和 Alarmes 表 1 idRegisto 。

警报表由触发器自动填充。我想连接两个表，并通过触发器使用表记录的值自动填充 idRegistos 表。

有谁可以帮帮我吗？我希望我已经很好地解释了我的疑问

谢谢

填充表警报的我的触发器

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


select lim_inf_temp, lim_sup_temp, lim_sup_humid, lim_inf_humid, lim_pt_orvalho into    @tempmin, @tempmax, @hummax, @hummin, @orvalho 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,"ERROR");
end if; 


end $$;

DELIMITER  ;

Answer 1

In alarm table, do you want to use the same newly generated idRegisto of registos table? - Ravinder

Yes. This is what i want. – user3320956

要在 alarm 表中插入新生成的相同 idRegisto 字段值，
如下更改触发器主体的一部分:

if ( CAST( NEW.Temperatura AS UNSIGNED ) < @tempmin ) then
     SELECT MAX( idAlarme ) into @maxidAlarme FROM alarmes;
     SET @maxidAlarme := @maxidAlarme + 1;

     SET @auto_idRegisto := ( SELECT AUTO_INCREMENT 
                              FROM INFORMATION_SCHEMA.TABLES
                              WHERE TABLE_NAME = 'registos'
                                AND TABLE_SCHEMA = DATABASE() );

     INSERT INTO alarmes( idAlarme, descricao_alarme, idRegisto ) 
     VALUES ( @maxidAlarme, "ERROR", @auto_idRegisto );
end if;