SQL trigger to get User ID column
If the application connects to SQL Server with service account credentials instead of current user credentials, the code will need to set CONTEXT_INFO, SESSION_CONTEXT, or save the user name to a table keyed by session_id as described in this answer. The value can then be used in the trigger code.  SESSION_CONTEXT is the most elegant but is only available in SQL Server 2016 and SQL Azure Database.