So I have 3 tables, person, hospital and hospitalized (as child of these 2)
So the point here is create a record in hospitalized if the person has symptoms in a hospital if this one has capacity.
At this point I created an after trigger on 'person' table
> CREATE DEFINER=`root`@`localhost` TRIGGER `person_AFTER_INSERT` AFTER
> INSERT ON `person` FOR EACH ROW BEGIN
> N
> /*CHECK ID EXISTS OR NOT.*/
> declare personExists int;
> declare hospital_id int;
> declare capacity int;
> select count(idperson) from person into personExists;
> select idhospital from hospital where capacity > 0 into hospital_id;
> select capacity from hospital where capacity > 0 and idhospital = hospital_id into capacity;
> if personExists > 0 then
> if symptoms = 'yes' then
> if capacity > 0 then
> /*INSERT ROW */
> insert into hospitalized(`person_id`,`hospital_id`) VALUES(person_id, hospital_id);
> /*UPDATE CAPACITY IN THAT HOSPITAL*/
> SET @i:=0;
> update hospital set capacity= @i:=(@i-1) where idhospital = hospital_id;
> end if;
> end if;
> end if;
> END
I do not know what's wrong Thanks a lot
Aucun commentaire:
Enregistrer un commentaire