Ms SQL now has a very good built in feature for temporal tables such that you shouldn't have to use audit tables, it's an out-of-the-box feature.

Only problem is that there's no way to delete historical data without DDL operations, which you need if a user demands to exercise their GDPR rights.