DELIMITER $$

DROP TRIGGER IF EXISTS `new_location`$$

CREATE TRIGGER `new_location` BEFORE INSERT ON `patient_case`
FOR EACH ROW
   CALL do_new_location(NEW.referral_source_id);
$$

DROP PROCEDURE IF EXISTS `do_new_location`$$

CREATE PROCEDURE do_new_location(IN location_id INT)
BEGIN
	DECLARE count INTEGER;
	SELECT COUNT(referral_source_id) INTO count FROM patient_case
        WHERE referral_source_id = location_id;
	IF count = 0 THEN
		INSERT INTO new_national_code(national_code,type)
			SELECT organisation_code, 'location' FROM referral_sources
            WHERE id = location_id;
	END IF;
END$$

DROP TRIGGER IF EXISTS `new_referrer`$$

CREATE TRIGGER `new_referrer` BEFORE INSERT ON `referrer_department`
FOR EACH ROW
   CALL do_new_referrer(NEW.referrer_id);
$$

DROP PROCEDURE IF EXISTS `do_new_referrer`$$

CREATE PROCEDURE do_new_referrer(IN clinician_id INT)
BEGIN
	DECLARE count INTEGER;
	SELECT COUNT(*) INTO count FROM requests r JOIN referrer_department rd
        on r.referrer_department_id = rd.id WHERE rd.referrer_id = clinician_id;
	IF count = 0 THEN
		INSERT INTO new_national_code(national_code,type)
			SELECT national_code, 'referrer' FROM referrers
            WHERE id = clinician_id;
	END IF;
END$$

DELIMITER ;