CODEWITHSUNDEEP
mysqltriggers
strange_098
strange_098

Reputation: 1361

MYSQL Insert values from another table - trigger

I have these tables in my database:

enter image description here

I want to add the registod and alarmes table one idRegisto .

The alarm table is populated automatically by a trigger. I would like to connect the two tables and the table alarmes populated idRegistos automatically by a trigger with the values ​​of table records.

Does anyone can help me please. I hope I have explained well my doubts

Thank you

enter image description here

My Trigger that populated table alarmes

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  ;

Upvotes: 0

Views: 254

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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

Yes. This is what i want. – user3320956

To insert the same newly generated idRegisto field value in alarm table,
Change part of your trigger body as below: 

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;

Upvotes: 1

Related Questions