 # @author: Allejo Chris G. Velarde
 # @date: February 13, 2013
 #
+
+DELIMITER $$
 
 ##----------------------
 # AFTER UPDATE institutions trigger.

CREATE TRIGGER institutions_au AFTER UPDATE on `institutions`
FOR EACH ROW
BEGIN
    SET @ACTIVE_INSTITUTION_STATUS = 9;
    SET @ACTIVE_SEARCH_TERM_STATUS = 1;
    SET @INACTIVE_SEARCH_TERM_STATUS = 0;
    
    -- clinic status has been changed from NON-ACTIVE to ACTIVE 
    IF OLD.`status` != @ACTIVE_INSTITUTION_STATUS AND NEW.`status` = @ACTIVE_INSTITUTION_STATUS THEN
    
        -- update statuses of existing search terms referencing this medical center to ACTIVE_SEARCH_TERM_STATUS
        UPDATE `search_terms` SET `status` = @ACTIVE_SEARCH_TERM_STATUS 
        WHERE `institution_id` = OLD.`id`; 
        
    -- clinic status has been changed from ACTIVE to NON-ACTIVE
    ELSEIF OLD.`status` = @ACTIVE_INSTITUTION_STATUS AND NEW.`status` != @ACTIVE_INSTITUTION_STATUS THEN
        -- update statuses of existing search terms referencing this medical center to INACTIVE_SEARCH_TERM_STATUS
        UPDATE `search_terms` SET `status` = @INACTIVE_SEARCH_TERM_STATUS 
        WHERE `institution_id` = OLD.`id`;
    END IF;  
END; $$
### end institutions_au trigger definition



BEGIN
    -- deletes search terms referencing this medical center
    DELETE FROM `search_terms` WHERE `institution_id` = OLD.`id`;
END $$
### end institutions_ad trigger definition

DELIMITER ;