First of all, regarding triggers, many large companies prohibit their use. First, they have poor portability, and second, they affect performance. This is what I strongly advocate.

Then let’s focus on the situation of non-physical deletion. It's really painful when you encounter a table with unique key constraints and a column like is_delete. This is how I handle it in the project. Assume that the user table user has these columns:

• id (primary key)

• username (unique key)

• ...

• is_delete

When inserting, if the unique key conflicts, check whether there is a deleted user with the same name:

SELECT id FROM user WHERE username = ? AND is_delete = 1

If you have the same name (and got the id), do an UPDATE operation and it will be treated as a deletion:

UPDATE user SET ..., is_delete = 0 WHERE id = ?

Then the painful problem comes. Since users have the need to delete (to be honest, this kind of need is rare), there is also the need to change the user name. Changing the user name encounters a primary key conflict, and the existing user has been logically deleted. So should you let him change it or not?

The expedient is to make the unique key "non-physical". Check it every time before creating a user. If you find a user with the same name that has not been deleted, you will allow the creation. The same goes for renaming. However, this operation may need to be turned into a transaction, because in the case of high concurrency, it is entirely possible that the SELECT does not have the same name, but the INSERT has the same name.

Question upstairs
"When changing the user name, a primary key conflict occurs, and the existing user has been logically deleted, so do you let him change it or not?"
There is a way to do it in a small project before I have used it before, but I don’t know if it is feasible for large projects.
Each table uses an auto-incrementing ID, and the primary key constraint uses the primary key + "is_delete" constraint.