Skip to content



This hook allows you to define MySQL triggers.


Using the hooks causes them not to clash with core or other extension triggers. They are compiled into one trigger with core triggers.


Once the function is created, visit the following URL to rebuild triggers and create to create the new trigger:


hook_civicrm_triggerInfo(&$info, $tableName)


  • array $info - array of triggers to be created, with index members:
    • mixed table - a string table name or an array of string table names
      • TIP: cast to array if looping
    • string when - BEFORE or AFTER
    • array event - an array of applicable events INSERT, UPDATE, or DELETE
    • string sql - the SQL string to run as the trigger
  • string $tableName - the changed table if called for a single table, e.g. when adding/editing custom fields, otherwise NULL


  • NULL


Add trigger to update custom region field based on postcode (using a lookup table)

Note that this example uses hard-coded a prioritisation of location types (since it was customer specific code and unlikely to change).

function regionfields_civicrm_triggerInfo(&$info, $tableName) {
  $table_name = 'civicrm_value_region_13';
  $customFieldID = 45;
  $columnName = 'region_45';
  $sourceTable = 'civicrm_address';
  $locationPriorityOrder = '1, 3, 5, 2, 4, 6'; // hard coded prioritisation of addresses
  $zipTable = 'CANYRegion';
  if(civicrm_api3('custom_field', 'getcount', array('id' => $customFieldID, 'column_name' => 'region_45', 'is_active' => 1)) == 0) {

  $sql = "
    REPLACE INTO `$table_name` (entity_id, $columnName)
    SELECT  * FROM (
      SELECT contact_id, b.region
      civicrm_address a INNER JOIN $zipTable b ON a.postal_code =
      WHERE a.contact_id = NEW.contact_id
      ORDER BY FIELD(location_type_id, $locationPriorityOrder )
    ) as regionlist
    GROUP BY contact_id;
  $sql_field_parts = array();

  $info[] = array(
      'table' => $sourceTable,
      'when' => 'AFTER',
      'event' => 'INSERT',
      'sql' => $sql,
  $info[] = array(
      'table' => $sourceTable,
      'when' => 'AFTER',
      'event' => 'UPDATE',
      'sql' => $sql,
  // For delete, we reference OLD.contact_id instead of NEW.contact_id
  $sql = str_replace('NEW.contact_id', 'OLD.contact_id', $sql);
  $info[] = array(
      'table' => $sourceTable,
      'when' => 'AFTER',
      'event' => 'DELETE',
      'sql' => $sql,