Trigger for a goal

0

I want to create a trigger to determine a goal for my hospital system (college project). Every time the doctor performed 10 appointments, his salary would double. However, it does not work. I think I'm not doing it right, so I'd like help structuring it right.

insert into medico values (9222, 2222, 8888, 15000);
/*CPF, CRM, CRM do gerente, e salário, respecificamente*/

create table medico_consultas (crm_fk int, qtd int, FOREIGN KEY(crm_fk) 
REFERENCES Medico (crm));

insert into medico_atende_paciente values(8080, 1, '2018-05-05');
/*CRM do médico, código do cadastro do paciente e data da consulta, 
respectivamente */


/* Trigger da meta */
delimiter $ 

create trigger medico_consulta_insert after insert on medico_atende_paciente
for each row

begin

if not exists (select medico_consultas.crm_fk from medico_consultas) then
insert into medico_consultas values (new.crm_fk, null);

if (qtd < 10) then
update medico_consultas set new.qtd = new.qtd + 1
where medico_atende_paciente.crm_fk = medico_consultas.crm_fk;

else 
update medico set salario = salario*2
where medico.crm = medico_atende_paciente.crm_fk;

end if;

end if;

end $ 

delimiter ;
    
asked by anonymous 30.11.2018 / 01:38

0 answers