Custom Database Triggers disappearing

Question

One of the ways I have set up my system to auto-increment a value (registration number) in a custom table is by using a custom database trigger. They are quite straight forward - after insert, update with the next value.
I have developed 2 triggers that seems to work well for me, however, I'm not entirely certain why but the triggers are disappearing on me over time. One trigger is on the civicrm_membership table (updates a civicrm_value table) and a civicrm_value table. This is one of them:

CREATE TRIGGER `Increment_Dog_Number` 
  BEFORE INSERT ON `civicrm_value_registered_dogs_5`  
  FOR EACH ROW 
    SET NEW.ddog_number_19 > = (
      SELECT MAX(ddog_number_19)+1 FROM civicrm_value_registered_dogs_5
    )

I'm not sure what is causing the triggers to be dropped -- any suggestions on how to work around it?

Thanks Norm

Answer 1

CiviCRM periodically rebuilds triggers, and when doing so it drops ALL existing triggers - see \Civi\Core\SqlTriggers::rebuild().

One solution to this may be to create an extension which adds your custom trigger to the list which CiviCRM will generate when performing this task. That way your trigger will persist through CiviCRM's schema rebuilds. See hook_civicrm_trigger_info() for this.

Another may be to configure that custom field to be an auto-increment or calculated value. I thought there was a way to add incrementing fields directly via the CiviCRM interface, but the only method I see on a local test site is setting the value to "View only" then populating it from PHP. You could implement equivalent functionality to the trigger above from a custom extension, perhaps hook_civicrm_post() will suit your needs for this.

Generally, it's only recommended to interact with CiviCRM's schema via the API interfaces CiviCRM provides. The application defines (and redefines, as you've discovered!) the underlying data structures, and directly modifying the database (at least, the tables belonging to CiviCRM) can have unexpected results.

Answer 2

Resolved this issue by eliminating the triggers and creating an extension with php code to essentially do the same thing using the hook_civicrm_post.