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

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

SCENARIO: There is a web-app wherein they log in using their credentials in users table. After inserting row in inserttest table, trigger activates in which it creates new row in log_audit table with one of the values as the userid of the user who is currently logged in in the web. Is this possible using only SQL Server?

SCRIPT:

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

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