1

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

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.

  • Crap. I was afraid of that. I do have a php script that does increment a column when the front-end calls the insert BUT the same doesn't happen when the back-end is used to create the contact for example. Is there a different hook to use for when a contact is created from the CiviCRM backend and one to execute after a membership is created for a user? To be clear, it is an extension that I wrote to increment a value. – Norm Dressler May 29 '17 at 0:18
  • I'd probably use hook_civicrm_post for this, it should be fired for all entity updates. There's also hook_civicrm_trigger_info if DB triggers are your thing. Answer updated. – Chris Burgess May 29 '17 at 0:24
  • You use the same hook for both cases, and perform appropriate response based on what entity and updates are passed in. (Maybe ask a new question for more on that if you need it?) – Chris Burgess May 29 '17 at 0:27
  • Ok, that is exactly what I'm doing but then checking to see what kind of post... function incrementrp_civicrm_post( $op, $objectName, $objectId, &$objectRef ) { if ($op == 'create' && $objectName == 'Membership') { – Norm Dressler May 29 '17 at 0:29
  • The above doesn't seem to work when I add a membership from the backend? – Norm Dressler May 29 '17 at 0:30
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.