/* 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 ;