SQL trigger to get User ID column

Question

I would like to achieve the following:

Users log into a web app, using their credentials from the users table.

After inserting a row into the inserttest table, a trigger activates in which it creates a new row in the log_audit table, with one of the values as the userid of the user who is currently logged in.

Is it possible to script this entirely in T-SQL? I can't seem to get my trigger to work properly.

Webapp connects via service account. It only uses one login in SQL Server (the one that is used in the connection string in the webconfig).

This is what I have:

CREATE TABLE inserttest (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL,
)

CREATE TABLE users (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL
)

CREATE TABLE log_audit (
  logid INT PRIMARY KEY IDENTITY,
  userid INT NOT NULL,
  logdate DATE NULL
)

CREATE TRIGGER tgr_insert ON inserttest
AFTER UPDATE, INSERT
AS BEGIN
  INSERT INTO log_audit (userid, logdate)
  VALUES (@USERIDofCURRENTAPPUSERinUSERSTABLE, GETDATE)
END -- trigger script will not work of course

Answer 1

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.