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?