Custom Database Triggers disappearing
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.