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 ;