Answers.Body: <p>CiviCRM periodically rebuilds triggers, and when doing so it drops ALL existing triggers - see <a href="https://github.com/civicrm/civicrm-core/blob/3b8eef99f18e4d26b5590e08da1bff500ca86bd8/Civi/Core/SqlTriggers.php#L66" rel="nofollow noreferrer"><code>\Civi\Core\SqlTriggers::rebuild()</code></a>.</p>
  
  <p>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 <a href="https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_trigger_info/" rel="nofollow noreferrer"><code>hook_civicrm_trigger_info()</code></a> for this.</p>
  
  <p>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 <a href="https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_post/" rel="nofollow noreferrer"><code>hook_civicrm_post()</code></a> will suit your needs for this.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:18:20.387Z
Answers.Text: 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.
Answers.UserDisplayName: Norm Dressler
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:24:08.820Z
Answers.Text: I'd probably use [`hook_civicrm_post`](https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_post/) for this, it should be fired for all entity updates. There's also [`hook_civicrm_trigger_info`](https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_trigger_info/) if DB triggers are your thing. Answer updated.
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:27:50.720Z
Answers.Text: 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?)
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:29:15.850Z
Answers.Text: 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') 
     {
Answers.UserDisplayName: Norm Dressler
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:30:18.473Z
Answers.Text: The above doesn't seem to work when I add a membership from the backend?
Answers.UserDisplayName: Norm Dressler
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T00:41:09.920Z
Answers.Text: Ok, nevermind -- the extension is now working.  Very odd because it wasn't working earlier today.  This means I can remove the triggers...
Answers.UserDisplayName: Norm Dressler
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-28T23:56:58.500Z
Answers.LastActivityDate: 2017-05-29T00:26:24.337Z
Answers.LastEditDate: 2017-05-29T00:26:24.337Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T14:15:43.837Z
Answers.LastActivityDate: 2017-05-29T14:15:43.837Z
Answers.UserDisplayName: Norm Dressler
Body: <p>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.<br>
  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:</p>
  
  <pre><code>CREATE TRIGGER `Increment_Dog_Number` 
    BEFORE INSERT ON `civicrm_value_registered_dogs_5`  
    FOR EACH ROW 
      SET NEW.ddog_number_19 &gt; = (
        SELECT MAX(ddog_number_19)+1 FROM civicrm_value_registered_dogs_5
      )
  </code></pre>
  
  <p>I'm not sure what is causing the triggers to be dropped -- any suggestions on how to work around it?</p>
  
  <p>Thanks
  Norm</p>
  
Tags: <database>
Title: Custom Database Triggers disappearing
ThreadText: # Custom Database Triggers disappearing
  
  (Asked by: username_0 on 2017-05-28)
  
  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
  username_0
  
  **Tags:** database
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2017-05-28. Score: 2 | Accepted answer
  
  CiviCRM periodically rebuilds triggers, and when doing so it drops ALL existing triggers - see [`\Civi\Core\SqlTriggers::rebuild()`](https://github.com/civicrm/civicrm-core/blob/3b8eef99f18e4d26b5590e08da1bff500ca86bd8/Civi/Core/SqlTriggers.php#L66).
  
  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()`](https://docs.civicrm.org/dev/en/master/hooks/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()`](https://docs.civicrm.org/dev/en/master/hooks/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.
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_1**: I'd probably use [`hook_civicrm_post`](https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_post/) for this, it should be fired for all entity updates. There's also [`hook_civicrm_trigger_info`](https://docs.civicrm.org/dev/en/master/hooks/hook_civicrm_trigger_info/) if DB triggers are your thing. Answer updated.  
  > **username_1**: 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?)  
  > **username_0**: 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')     {  
  > **username_0**: The above doesn't seem to work when I add a membership from the backend?  
  > **username_0**: Ok, nevermind -- the extension is now working.  Very odd because it wasn't working earlier today.  This means I can remove the triggers...  
  
  ---
  
  ### Answer by username_0 on 2017-05-29. Score: 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.
  
  
  ---
  
  