[Trigger Mysql] Redução estoque muitos para muitos

É o seguinte:

Tenho uma tabela de consultas, quer que quando uma consulta seja realizada (UPDATE na tabela consulta, muda o status de Pendente para Realizada) diminua a quantidade na tabela produtos, mas aí que vem o problema.

Eu quero que seja diminuido a quantidade na tabela produtos só quando for selecionado no programa um procedimento (Exame), a parte de só se for selecionado o procedimento eu já resolvi, agora falta diminuir a quantidade na tabela produtos, só que cada exame possui vários produtos, assim como cada produto pode estar em vários procedimentos.

Exemplo:

Agulha -> Exame Qualquer 1
Agulha -> Exame Qualquer 2
Agulha -> Exame Qualquer 3
Algodão -> Exame Qualquer 2
Curativo -> Exame Qualquer 2
Água Oxigenada -> Exame Qualquer 1

Minha dúvida é:

Como vou diminuir todos os produtos de determinado exame em uma trigger?

Se puderem me ajudar, desde já agradeço. Aprendi triggers faz pouco tempo, então não sei muita coisa.

Preciso dela para terminar um trabalho da faculdade

Outros infos:

Tabela Consultas

create table Consultas(
	id int auto_increment not null,
	data_consulta date not null,
	status char(1) not null,
	retorno date,
	diagnostico varchar(300),
	observacao varchar(300),
	id_cliente int not null,
	id_funcionario int not null,
	id_procedimento int not null,
	id_remedio int  not null,
	valor double not null,
	foreign key (id_cliente) references Clientes(id),
	foreign key (id_funcionario) references Funcionarios(id),
	foreign key (id_procedimento) references Procedimentos(id),
	foreign key (id_remedio) references Remedios(id),
	primary key (id,id_cliente,id_funcionario)
);

Tabela Procedimentos-Produtos

create table ProcedimentosProdutos(
	id_procedimentos int not null,
	id_produtos int not null,
	foreign key (id_produtos) references Produtos(id),
	foreign key (id_procedimentos) references Procedimentos(id),
	primary key (id_procedimentos,id_produtos)
);

Tabela Procedimentos

create table Procedimentos(
	id int primary key auto_increment not null,
	nome varchar(100) not null,
	valor double not null
);

Tabela Produtos

create table Produtos(
	id int auto_increment not null,
	nome varchar(100) not null,
	quantidade int not null,
	id_fornecedor int not null,
	valor double not null,	
	foreign key (id_fornecedor) references Fornecedor(id),
	primary key (id,id_fornecedor)
);

Trigger atual, quando atualiza a consulta

DELIMITER ||



DROP TRIGGER IF EXISTS trigUpdateConsulta ||
CREATE TRIGGER trigUpdateConsulta AFTER UPDATE ON Consultas
	FOR EACH ROW BEGIN
		DECLARE nor1_observacao VARCHAR(300);	
		DECLARE nor1_diagnostico VARCHAR(300);	
		DECLARE nor1_retorno VARCHAR(20);
			
		DECLARE nor2_observacao VARCHAR(300);	
		DECLARE nor2_diagnostico VARCHAR(300);
		DECLARE nor2_retorno VARCHAR(20);

		DECLARE not1_id INT DEFAULT 0;	
		DECLARE not1_data_consulta INT DEFAULT 0;	
		DECLARE not1_status INT DEFAULT 0;
		DECLARE not1_id_cliente INT DEFAULT 0;	
		DECLARE not1_id_funcionario INT DEFAULT 0;	
		DECLARE not1_id_procedimento INT DEFAULT 0;
		DECLARE not1_id_remedio INT DEFAULT 0;	
		DECLARE not1_valor INT DEFAULT 0;

		DECLARE not2_id INT DEFAULT 0;	
		DECLARE not2_data_consulta INT DEFAULT 0;	
		DECLARE not2_status INT DEFAULT 0;
		DECLARE not2_id_cliente INT DEFAULT 0;	
		DECLARE not2_id_funcionario INT DEFAULT 0;	
		DECLARE not2_id_procedimento INT DEFAULT 0;
		DECLARE not2_id_remedio INT DEFAULT 0;	
		DECLARE not2_valor INT DEFAULT 0;	

		DECLARE aux_id_remedio INT DEFAULT 0;
		DECLARE tem_remedio INT DEFAULT 0;

		DECLARE aux_id_procedimento INT DEFAULT 0;
		DECLARE tem_procedimento INT DEFAULT 0;

		DECLARE var_falta VARCHAR(1000);
			SET var_falta = 'A variaveis a seguir não podem ser nulas: ';
			

		IF(NEW.id IS NULL) THEN
			SET not1_id = 1;
		END IF;
		IF(NEW.data_consulta IS NULL) THEN
			SET not1_data_consulta = 1;
		END IF;
		IF(NEW.status IS NULL) THEN
			SET not1_status = 1;
		END IF;
		IF(NEW.id_cliente IS NULL) THEN
			SET not1_id_cliente = 1;
		END IF;
		IF(NEW.id_funcionario IS NULL) THEN
			SET not1_id_funcionario = 1;
		END IF;
		IF(NEW.id_procedimento IS NULL) THEN
			SET not1_id_procedimento = 1;
		END IF;
		IF(NEW.id_remedio IS NULL) THEN
			SET not1_id_remedio = 1;
		END IF;
		IF(NEW.valor) THEN
			SET not1_valor = 1;
		END IF;


		IF (NEW.data_consulta IS NULL) THEN
		   SET nor1_observacao ='Não Possui';
		END IF;
		IF (NEW.diagnostico IS NULL) THEN
		   SET nor1_diagnostico='Não Possui';
		END IF;
		IF (NEW.retorno IS NULL) THEN
		   SET nor1_retorno='Não Possui';
		END IF;

		
		IF(OLD.id IS NULL) THEN
			SET not2_id = 1;
		END IF;
		IF(OLD.data_consulta IS NULL) THEN
			SET not2_data_consulta = 1;
		END IF;
		IF(OLD.status IS NULL) THEN
			SET not2_status = 1;
		END IF;
		IF(OLD.id_cliente IS NULL) THEN
			SET not2_id_cliente = 1;
		END IF;
		IF(OLD.id_funcionario IS NULL) THEN
			SET not2_id_funcionario = 1;
		END IF;
		IF(OLD.id_procedimento IS NULL) THEN
			SET not2_id_procedimento = 1;
		END IF;
		IF(OLD.id_remedio IS NULL) THEN
			SET not2_id_remedio = 1;
		END IF;
		IF(OLD.valor) THEN
			SET not2_valor = 1;
		END IF;


		IF (OLD.observacao IS NULL) THEN
		   SET nor2_observacao='Não Possui';
		END IF;
		IF (OLD.diagnostico IS NULL) THEN
		   SET nor2_diagnostico='Não Possui';
		END IF;
		IF (OLD.retorno IS NULL) THEN
		   SET nor2_retorno='Não Possui';
		END IF;

		IF(not1_id = 0 AND not1_data_consulta = 0 AND not1_status = 0 AND not1_id_cliente = 0 AND not1_id_funcionario = 0 AND not2_id = 0 AND not2_data_consulta = 0 AND not2_status = 0 AND not2_id_cliente = 0 AND not2_id_funcionario = 0) THEN
			INSERT INTO AuditoriaConsulta (
				OLDid, 
				OLDdata_consulta, 
				OLDstatus, 
				OLDretorno, 
				OLDdiagnostico, 
				OLDobservacao, 
				OLDid_cliente, 
				OLDid_funcionario, 
				OLDid_procedimento, 
				OLDid_remedio, OLDvalor,

				NEWid, 
				NEWdata_consulta,
				NEWstatus, 
				NEWretorno, 
				NEWdiagnostico, 
				NEWobservacao, 
				NEWid_cliente, 
				NEWid_funcionario, 
				NEWid_procedimento, 
				NEWid_remedio, 
				NEWvalor) 
			VALUES(
				OLD.id, 
				OLD.data_consulta, 
				OLD.status, 
				OLD.retorno, 
				OLD.diagnostico, 
				OLD.observacao, 
				OLD.id_cliente, 
				OLD.id_funcionario, 
				OLD.id_procedimento, 
				OLD.id_remedio, OLDvalor,

				NEW.id, 
				NEW.data_consulta,
				NEW.status, 
				NEW.retorno, 
				NEW.diagnostico, 
				NEW.observacao, 
				NEW.id_cliente, 
				NEW.id_funcionario, 
				NEW.id_procedimento, 
				NEW.id_remedio, 
				NEW.valor);
		END IF;

		IF(NEW.id_remedio IS NULL) THEN
			SET aux_id_remedio = 1;
		ELSE
			SET tem_remedio = (select 
						rem.quantidade
					   from 
						consultas con,
						remedios rem
					   where
						rem.id = NEW.id_remedio and
						rem.quantidade > 0
					   group by 
						rem.quantidade);
		END IF;

		IF(aux_id_remedio = 0) THEN
			UPDATE remedios SET quantidade = tem_remedio - 1 where id = NEW.id_remedio;
		END IF;

	END;

||


DELIMITER ;

Topicos relacionados