jeudi 29 juillet 2021

challenge about corona as a practice exercise second part

So I have 3 tables, person, hospital and hospitalized (as child of these 2)

enter image description here

enter image description here

enter image description here

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