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.

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