MYSQL Insert values from another table - trigger

Question

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  ;

Did you define referential constraint in alarm table on idRegisto column? How do you want to store it? — Ravinder Reddy
– Ravinder Reddy, Commented Mar 28, 2014 at 9:47
Unless you add what you want on idRegisto, your posting is incomplete. — Ravinder Reddy
– Ravinder Reddy, Commented Mar 28, 2014 at 10:10
idRegisto will be automatically incremented. Is basically an id from table registos. — strange_098
– strange_098, Commented Mar 28, 2014 at 10:13
How do want to use it in alarm table? Using a trigger? And also see my first comment on top. — Ravinder Reddy
– Ravinder Reddy, Commented Mar 28, 2014 at 10:14

Ravinder Reddy · Accepted Answer · 2014-03-28 10:30:47Z

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

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;

answered Mar 28, 2014 at 10:30

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

7 Comments

strange_098 Over a year ago

Very Thanks for help.Could you tell me how to change the alarm table to take the idRegisto as a foreign key reference to the table registos please?

strange_098 Over a year ago

I'll test the code that you kindly gave me. But first I need to link the two tables by foreign key. That's why I asked for help it.

strange_098 Over a year ago

I will acept your answer but before I need to test.

strange_098 Over a year ago

Could you tell me how to change the alarm table to take the idRegisto as a foreign key reference to the table registos please?

Ravinder Reddy Over a year ago

In Alarm table add the idRegisto column with the same data type and size as in registos table and add referential constraint as foreign key (idRegisto) references registos(idRegisto).

|

Collectives™ on Stack Overflow

MYSQL Insert values from another table - trigger

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related