/* in MySQL 5.0x need SUPER priviledges; in MySQL 5.1x can use 'GRANT TRIGGER' */

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 LocationId INT)
BEGIN
	DECLARE count INTEGER;
	SELECT COUNT(referral_source_id) INTO count FROM patient_case
        WHERE referral_source_id = LocationId;
	IF count = 0 THEN /* REPLACE INTO in case entry exists from deleted record: */
		REPLACE INTO new_national_code(national_code,type)
			SELECT organisation_code, 'location' FROM referral_sources
            WHERE id = LocationId;
	END IF;
END$$

DROP TRIGGER IF EXISTS `new_referrer_insert`$$
CREATE TRIGGER `new_referrer_insert` BEFORE INSERT ON `requests`
    FOR EACH ROW CALL do_new_referrer(NEW.referrer_department_id)$$

DROP TRIGGER IF EXISTS `new_referrer_update`$$
CREATE TRIGGER `new_referrer_update` BEFORE UPDATE ON `requests`
    FOR EACH ROW CALL do_new_referrer(NEW.referrer_department_id)$$

DROP PROCEDURE IF EXISTS `do_new_referrer`$$
CREATE PROCEDURE do_new_referrer(IN ReferrerDepartmentID INT)
BEGIN
	DECLARE ReferrerID INTEGER;
	DECLARE count INTEGER;
	
	SELECT referrer_id INTO ReferrerID FROM referrer_department
        WHERE id = ReferrerDepartmentID;
	SELECT COUNT(*) INTO count FROM requests r 
		JOIN referrer_department rd on r.referrer_department_id = rd.id
		WHERE rd.referrer_id = ReferrerID;
	IF count = 0 THEN /* REPLACE INTO in case entry exists from deleted record: */		
		REPLACE INTO new_national_code(national_code,type)
			SELECT national_code, 'referrer' FROM referrers WHERE id = ReferrerID;
	END IF;
END$$

DELIMITER ;