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 ;