Which is fired first? Welcome email or trigger?

Ok, I tested again without any condition and it still doesn't work. I still the firstname+lastname as the username in the email.

The query itself works, I tested them in phpMyAdmin and when I click the 'Approve' button in CB > User Management the username is updated to the membership number.

I have tried onUserActive, onBeforeUserActive and even before/after user sync but none worked.

I don't understand why it's a trial and error process. I know you have a list of triggers in your tutorial but there must be a developer documentation that tells exactly when each trigger fires.

I have tried onUserActive, onBeforeUserActive and even before/after user sync but none worked.

All the triggers you've tried are the right triggers. They all execute before user approval. CB Queries is updating the database; it does NOT update the user object, which is why the email does not contain the new username. This is why CB Fields exists, it acts directly on the user object.

but there must be a developer documentation that tells exactly when each trigger fires.

No such thing exists.

Hmm .. with CB Fields I can't have complex query. I need to get the next member number first before assigning it to the username.

Anyway, I wanted to hide the username from the welcome email and use

Hmm .. with CB Fields I can't have complex query. I need to get the next member number first before assigning it to the username.

Then you'll probably need a new integration to handle your specific case.

Anyway, I wanted to hide the username from the welcome email and use

The password substitution can only be used in the Pending email unless password generation is used in which case it should be in the Welcome AND Pending email. Confirm this working fine on CB 1.7.