 CREATE TRIGGER institutions_au AFTER UPDATE on `institutions`
 FOR EACH ROW
 BEGIN
+    DECLARE _city_name VARCHAR(250);
     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 
+
+    -- 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`; 
-        
+        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 
+        UPDATE `search_terms` SET `status` = @INACTIVE_SEARCH_TERM_STATUS
         WHERE `institution_id` = OLD.`id`;
-    END IF;  
-END; $$
+    END IF;
+
+    IF OLD.city_id IS NOT NULL AND NEW.city_id IS NULL THEN
+        UPDATE search_terms SET city_id = NULL, city_name = NULL WHERE institution_id = OLD.id;
+    ELSEIF (OLD.city_id IS NULL AND NEW.city_id IS NOT NULL) OR OLD.city_id != NEW.city_id THEN
+        SELECT name INTO _city_name FROM cities WHERE id = NEW.city_id;
+        UPDATE search_terms SET city_id = NEW.city_id, city_name = _city_name WHERE institution_id = OLD.id;
+    END IF;
+END $$
 ### end institutions_au trigger definition
 
 

# @author: Allejo Chris G. Velarde
# @date: February 13, 2013
#

##----------------------
# AFTER UPDATE institutions trigger.

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

DELIMITER ;