Trigger to insert values from another table - Mysql

Question

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.

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.

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

enter image description here

Daniel Alonso · Accepted Answer · 2014-03-31 11:16:19Z

1

You can use an "after insert trigger"

AFTER INSERT ON 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.

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

edited Mar 31, 2014 at 11:16

answered Mar 31, 2014 at 10:56

Daniel Alonso

607 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

strange_098 Over a year ago

Thanks fpr reply. could you explain in a most practical way please?

Collectives™ on Stack Overflow

Trigger to insert values from another table - Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related